SQL help in access
Hey guys new to the forum but I am getting better at programming. I made a database to keep track of inventory items that we have and sold. This database keeps items even after they were used. A simplified version is this
(Item, serial, job) Here is an example list
Item1, 01101, 0
Item1, 02340, 0
Item1, 03244, 1
Item2, 02343, 1
Item2, 02343, 1
Item3, 32432, 0
Item3, 23432, 1
Now to how many of each item I have I used SQL to combine the ones with a 0 in job meaning it hasn't been used.
select item, count(item) from DB where job = 0 group by item
That works fine except it won't display Item2 because there is no Item2 with a job that equals 0, I need to display Item 2 with a 0 next to it but I am having trouble figuring out how to do this. I was thinking mabey joining a table with all the items might work. Any help would be appreciated.
Thanks,
Al
[943 byte] By [
dajawu] at [2007-11-11 8:42:34]
# 2 Re: SQL help in access
If you have two tables:
item, serial, job
and
item, itemname, value, whatever
You can join the two using an outer join and group by the column on the item table instead of the itemjobs table.
Should work fine. If you could post the create statements for each table we could write a statement easy enough.
# 3 Re: SQL help in access
I made a quick database in Access. Take a look at it and the query. Notice how if I used all of one certain color it won't show up in the query. I need a way around this.
dajawu at 2007-11-11 23:49:01 >
# 4 Re: SQL help in access
Does this do the trick?
SELECT color,count(color)
FROM Inventory WHERE Jobnum = 0 Group by color;
