Problems with creating a query

[microsoftgirl]

I'm creating a query which includes multiple tables. A Product Details table, Transaction details table, and a Delivery details table.

I have set up a relationship between these tables with the ProductID.
Firstly I want to calculate the amount of Deliveries that the company have had. I have included ProductID and Delivery Quantity from the Delivery details table in the query. The ProductID total has been set to "Group by" and the Delivery Quantity has been set to Sum.

When I run the query, the total number of deliveries are being calculated correctly. However, the first result(First product) is doubling whatever the actual result is.

Does anyone know why it is doing this or how I can fix this? Try to keep it a little simple also, I'm new to databases.

 

[Alansidman]

Suggest you post your SQL statement or upload a copy of your db. If you upload, run a compact and repair and dummy up any confidential information.

 

[Lightwave]

It is likely that this is due to set mathematics relating to the joins between the tables. For example if a child and a parent tables are linked and you group on unique records in the children table it will appear as if there are multiple parent records when there are not.

One solution can be to alter the nature of the join within the query although you have to be careful as some joins between tables are limited as to how they can be explained. Another solution is to vary the column you group on.

An example PKID / Name
Mothers of children
1 - Jane Eyre

Children FKID / Child's name
1 - Rebecca Eyre
1 - Susan Eyre
1 - Colin Eyre

Now if you do a query on the two tables and group on children's name in child table but bring across the mothers name you will get

Mothers name / FKID / Child's name
Jane Eyre / 1 / Rebecca
Jane Eyre / 1 / Susan Eyre
Jane Eyre / 1 / Colin Eyre

Now it looks like there are three Jane Eyre's... No its just for each of the children Jane is the mother. You will however be allowed to count up the number of Jane Eyre's in the query and it will indicate 3. In this case it is obvious that there is only one Jane Eyre but the implementation of the query makes it appear as if there is 3. Doing this over thousands of records this may not be obvious.

Be careful how you construct your query and what you group on it is unlikely anyone here can be certain of your problem without further understanding of the specifics but this should be a pointer to your problem.

 

[microsoftgirl]

It is likely that this is due to set mathematics relating to the joins between the tables. For example if a child and a parent tables are linked and you group on unique records in the children table it will appear as if there are multiple parent records when there are not.

One solution can be to alter the nature of the join within the query although you have to be careful as some joins between tables are limited as to how they can be explained. Another solution is to vary the column you group on.

An example PKID / Name
Mothers of children
1 - Jane Eyre

Children FKID / Child's name
1 - Rebecca Eyre
1 - Susan Eyre
1 - Colin Eyre

Now if you do a query on the two tables and group on children's name in child table but bring across the mothers name you will get

Mothers name / FKID / Child's name
Jane Eyre / 1 / Rebecca
Jane Eyre / 1 / Susan Eyre
Jane Eyre / 1 / Colin Eyre

Now it looks like there are three Jane Eyre's... No its just for each of the children Jane is the mother. You will however be allowed to count up the number of Jane Eyre's in the query and it will correctly say 3.

Be careful how you construct your query and what you group on it is unlikely anyone here can be certain of your problem without further understanding of the specifics but this should be a pointer to your problem.

That was the exact problem I was having with the repeating ProductID(Mothers name). I think it's working correctly now, thank you.