going fruity

[damie]

I have one table with a list of 10 types of fruit. Another table details all the invoices throughout the year for these fruits. I also have 2 queries:

Query 1 is an overall summary of the types of fruit with their respective profits gained over the period of a year.
Query 2 is the same but for just March of that year

I am stuck trying to create a query of three columns (Fruit, Profit for the year, profit for the month) in which all the fruit appears regardless of whether any was sold during that time. If I have not sold pineapples during the year, pineapples will not appear in query 1 and if if I haven’t sold apples in March, they will not appear in query 2. When I create the three column query I will neither get pineapples nor apples appearing although I want this to be the case.

Can anybody help me in this?

Thanks

 

[ecniv]

Your queries (I guess) are using inner joins (as in the display is a single line with no arrow on it).

Use a left join from the fruits table to the invoices (fruit------>invoices) and use the nz function to return zero if the invoice is null. To change to left join, double click on the join line and change to read : All from fruit and only those that match from invoices.

Do the same for the other query. Drop both queries into a new query, and left join again, using the fruit id.


Vince

 

[damie]

Thanks very much for that advice, I've done what I think you wanted me to and it works for now. The only other question I have is that in the Nz function, rather than "0", I want "0,00€" to appear in empty cells but don't know how to do this ( Nz([YearTotal];0,00€) just gives me syntax errors.

Thanks again.

damie

 

[damie]

Just found out the answer - no need for a reply!