Hi John,
Thanks for your reply. I'm sorry I wasn't clear enough. I hope this is
better.
The idea of what I want to do is this: in this table the brand ([articlenr]),
amounts ([Filled-packs]) and prices ([Fillprice]) of the cigaretpacks a
cigarette-vendingmachine has been refilled with, are entered. I want to be
able to get a count of all machines with brand A and count how many there are
in it. If there aren't any in a machine, it shouldn't be left out but the
amount should be 0.
The other tables I have aren't really important so I didn't mention them.
What I should have mentioned was the recordnames in the table. They are:
Tablename= tblCounts2
1) Countnr (keyfield, no autonumber)
2) Shaftnr (numeric)
3) Articlenr (numeric, keyfield)
4) Filled-packs (amount of packs filled)
5) Pricecode (numeric, 1 or 2 only)
6) Fillprice (numeric)
7) Change (numeric)
A sample of the records:
1) 2) 3) 4) 5) 6) 7)
64 1 81 10 1 3,65 0,35
64 2 79 10 1 3,65 0,35
64 3 80 0 1 3,54 0,46
65 1 61 5 1 3,25 0,25
65 2 62 5 1 3,25 0,25
65 3 94 5 1 3,25 0,25
65 4 63 5 1 3,09 0,01
66 1 87 1 1 3,20 0,30
66 2 88 1 1 3,25 0,25
66 3 85 1 1 3,65 0,35
68 1 61 5 1 3,25 0,25
68 2 62 5 1 3,25 0,25
68 3 94 5 1 3,25 0,25
68 4 63 5 1 3,09 0,01
69 1 81 10 1 3,65 0,35
69 2 79 10 2 3,55 0,45
69 3 80 10 2 3,45 0,50
69 4 82 10 1 3,65 0,35
69 5 83 1 2 3,55 0,45
70 1 81 5 1 3,65 0,35
70 2 79 5 1 3,65 0,35
70 3 80 5 1 3,54 0,46
70 4 82 5 1 3,65 0,35
Let's say I want to check of the amount of Filled-packs for articlenr 81.
That should be:
64 10
69 10
70 10
That's what my present query would show. But I need:
64 10
65 0
66 0
68 0
69 10
70 10
I haven't been able to test your query yet but I will try to. If in the
meantime you could give above mentioned some thought I would be most
grateful!
Roland
