Duplicated values in query

[truent]

Hello. I'm just starting with Access 2007 and I'm having a problem with some values in a query.

So, I have 4 tables:

Project: ID, Project Name
Material: ID, Material Name
tblDelivered: ID, Project, Material Name,Quantity of Material Delivered


tblReturned: ID, Project, Material Name, Quantity of Material Returned

What I'm trying to do is to show in a query the project name, material name, quantity returned, quantity delivered and the total of those two quantities.

The problem arrives when I have two deliveries for the same material.

So, for example, I have three materials: (M1,M2,M3) and I had deliveries(10,20,30), returns(5,0,0) respectively, all with the same project. In addition, I had another delivery of M1 of 5 units.

What I want:
Proj Mat Deliv Return
P1 M1 10 5
P1 M1 5 0
P1 M2 20 0
P1 M2 10 0

What Access shows:
Proj Mat Deliv Return
P1 M1 10 5
P1 M1 5 5
P1 M2 20 0
P1 M2 10 0

Is there any way this can be accomplished? I'm out of ideas so if you could give a hint I would appreciate it.

Thanks in advance.

[aytee111]

Is there a way to link the returns to a specific delivery? Date or ID maybe?

[truent]

Hi. There is not one. However, now that you mentioned it, I think it's best to have a date for each delivery and return so I added those fields to my database.

So now, I have

Project: ID, Project Name
Material: ID, Material Name
tblDelivered: ID, Project, Material Name,Quantity of Material Delivered, Delivery Date
tblReturned: ID, Project, Material Name, Quantity of Material Returned, Return Date

I tried it on my file but I still have the same output as before:

What I want:
Proj Mat Deliv Return Date deliv Date returned
P1 M1 10 5 03/03/11 03/03/11
P1 M1 5 0 03/04/11
P1 M2 20 0 03/03/11
P1 M3 10 0 03/03/11

What Access shows:
Proj Mat Deliv Return Date deliv Date returned
P1 M1 10 5 03/03/11 03/03/11
P1 M1 5 5 04/03/11 03/03/11
P1 M2 20 0 03/03/11
P1 M3 10 0 03/03/11

Is that what you meant? Probably I'm doing something wrong with my relationships but I really don't know...

[aytee111]

Unless you have a way to tie the two together you cannot get what you want. Either link the returns to one particular delivery or else you will have to sum them:

P1 M1 15 5
P1 M2 20 0
P1 M2 10 0

In you second example you can link using the dlv and ret date - but is this always the case?

P1 M1 10 5 03/03/11 03/03/11
P1 M1 5 0 03/04/11
P1 M2 20 0 03/03/11
P1 M3 10 0 03/03/11

As far as data integrity goes, it is probably better to make sure that when the user enters a return quantity then it is tied to a particular delivery - otherwise a customer can be delivered 10 and return 15. Easy way for them to make money!

[truent]

I think I'm getting it. However, let's say I tied returns with deliveries.

I deliver 10 units of M1, and the next morning I deliver 40 of M1.

Then the next day, the client returns 41 units of M1. What happens in that case? Since the return quantity is greater than any of the deliveries the total would be a negative.

Does that makes sense?

[aytee111]

You wouldn't allow that to be entered. The user can only enter up to the quantity delivered. So, the Order ID of the first 10 units delivered will have a return quantity of 10. The user will then need to select the second Order ID and enter a return quantity of 31.

Your first example:
Proj Mat Deliv Return
P1 M1 10 5
P1 M1 5 0
needs to have something that differentiates between line 1 and line 2 - so it depends on what you are trying to achieve. Either order date or order number.

[truent]

I get it now. Thank you so much for your help. You were very helpful.

Cheers.