Query not displaying all data

[Chimp8471]

i am trying to run a query of production completed in totals, throughtout the year. i am trying to sort it by date and then also by machine size

for example

we have a number of formats

1ltr Baseline, 250ml slim, 1 ltr square etc

i need to know how much each format has produced on each day, if has produced zero then i need it to display zero.

the problem that i have is that i have designed a query but it is only displaying data where all formats have produced on the same day.

no where in my query does it display zero for any format.

hope this makes sense.

Cheers

Andy

 

[neileg]

Look at the join between your tables. In the QBE view, right click the join line and look at the options. It should be clear which one you need.

 

[Chimp8471]

thanks for the reply

i have attached another picture to highlight how my query is set up, i am probably going about it in the most difficult way possible.

when i change the properties within the query joins then try to run it i get an error box appear

saying that the :

sql statement could not be run because of ambiguos outer join

Please help

Andy

 

[Chimp8471]

whoops sorry, forgot to attach the picture

Andy

 

[neileg]

The picture doesn't help much because the tables line up to disguise how they are linked! Plus I can't read the print!

However, your arithmetic is going to fail where you have a null value in any of your fields. You need to use Nz() to turn these to zeros. Eg :
Total: Nz([1L Base Cases]) + Nz([1L combi Cases]) + .....

This may be all your problem is.

If it's not, come back.

PS You shouldn't have embedded spaces in your fields names. It may cause problems in certain circumstances. So [1L Base Cases] should be [1LBaseCases]

 

[Pat Hartman]

I see a table design issue here. Your column names seem to be data values rather than attribute names. Sort of like having a series of fields named Tom, Dick, and Harry rather than one called FirstName. I can't read the picture you posted either so I'm going to guess that you have too many tables designed and you probably need only one or two. If you can post some more details about your tables, I'm sure we can help you sort it out.

 

[Chimp8471]

once again thanks for taking in an interest and sorry about the picture,

the problem that i have is that someone else wrote this database and has since left my company so i am trying to pick up the bones, it is in quite a mess.

however it is functioning, to some extent.

i will attach it below, hopefully someone will be able to help.......

but to recap what i need is a query that displays how many cases or cartons were produced each day by type and by type i mean, 1ltr slim, Ilt Base etc as you will see when you run the query all formats only for records appear for daycodes

2343
2344
2346
2350

whereas in the production table there is clearly data for many more daycodes than this.

but even if nothing has been produced on a specific day i need it to display zero, but it is essential that for every day i have a figure for each format.

Hope you can see the mess i am dealing with........hope you can understand where i am going with this

Cheers

Andy

 

[neileg]

Forget all these individual queries and start again.

Create a query based on tblMachines and tblProduction. Change the link to include all records from tblMachines and those from tblProduction that match. Add the fields Area, Output(c) and DayCode. Make this a cross tab query with Area as a column heading, output(c) as a value (summed) and DateCode as a row.

I think this is what you want.

If you want date instead of date code, you need to create a query to bring this in to the prodiuction data and use the query instead of tblProduction.

If you want just one day, add a parameter to the DateCode field.