Counting multiple fields

[Arkturus292]

Hey everyone,​

Alright so here is what I have :

Table : Order
Customer___________Item 1_______ Item 2
Johnny_______________ steak__________ fries
Jimmy _______________hamburger
Arnold________________ rice___________ steak

I need to be able to count the number of record for each item. Any idea on how to do it? I have utterly failed on all my attemps.
The result expected for the example given would be:
Steak = 2
fries = 1
hamburger = 1
rice = 1

So far I've been able to make 2 querys (one for each item) that successfully tells me the amount of each item. Thought I could then just make a third query that would sum the two count...but somehow it doesn't work....

Thanks for the help,
now if you'll excuse me...all that just made me really hungry
Akturus292

 

[MarkK]

Hey there, welcome to the forum.
You have a table design problem. One record in one table should only ever represent one single thing, but you have a record that contains steak AND fries.
A recommended data structure for orders would have two tables as follows...

[B]tOrder[/B]
OrderID
Customer

[B]tOrderDetail[/B]
OrderDetailID
OrderID
Item

With this structure one order might have any number of items, or none, and you could sum or count the items very easily since each individual item is recorded as belonging to a particular OrderID.
Cheers,

 

[Arkturus292]

Thanks for the quick answer.
Actually I've managed to make the sum directly on my report which works. The only problem I have left is that when the second value is null, it writes error which is logical since 1+null= error.

So I need to figure a way to make my query give 0 to all null. Here is my query :

SELECT Count(demandes.no_lettre2) AS CompteDeno_lettre2, lettres.code_lettre
FROM lettres INNER JOIN demandes ON lettres.no_lettre = demandes.no_lettre2
GROUP BY lettres.code_lettre, lettres.date_fin
HAVING (((lettres.date_fin) Is Null) AND ((Max(demandes.date_demande))>=[Formulaires]![menu_rapport_statistique_lettre]![date_du] And (Max(demandes.date_demande))<=[Formulaires]![menu_rapport_statistique_lettre]![date_au]));

So far I've tried ''Count(NZ(demandes.no_lettre2,0)'' which didn't work. Any ideas?
Thanks alot

EDIT : Now that I think of it, I could use a kind of SUMIF for the field in my report, but I don't know how to do it. How would I do that ? :
Field1+Field2 (but only if Field2 has a value)...that should rid me of the error...