How to show a value only once per unique value in a different column?

[Darkzler]

Hi everyone,

First of all, I'm really new to Microsoft Access and I've only read through an access basic manual. So please keep that in mind when answering, pedagogical please hehe.

I got two tables that I want to merge, my unique value is a transportnumber. In the first table it is an unique value, in the second table the transportnumber exist in multiple rows.

The problem is, that since the tables are crossed I can see the transportnumber multiple times, with the finance shown correctly for each row but the amount of shipments is shown on each row which then is duplicates. For example, if the amount of shipments on a transport is 15 and there is two rows, it will show 15x2 which sums up to an incorrect sum of shipments on that specific transport.

Could you please help me out on how to show only unique values in: "Consignments" and "Payweight"?

Best regards,

Fredrik

 

[Joe4]

In the first table it is an unique value, in the second table the transportnumber exist in multiple rows.

For example, if the amount of shipments on a transport is 15 and there is two rows, it will show 15x2 which sums up to an incorrect sum of shipments on that specific transport.[?QUOTE}
I am confused. If it is a unique value in one table, then why do you have 15 x 2 and not 15 x 1.
Something doesn't seem to add up.

Can you post a sample for each table, before the query?
You may have a Table Design issue, or perhaps you need to join on multiple fields in your query.

 

[Darkzler]

Hi Joe4,

Thanks for taking the time to reply.

I've uploaded pictures on both tables, hope this helps.

 

[Joe4]

If I understand what I think you are trying to do, I think you might want to add a second join line in your query, on the "Trp Year Mo" field. That might fix your issue.

 

[Darkzler]

Hi Joe4,

I did the following (see pic) and got an error. Am I doing it wrong or perhaps misunderstood you?

 

[Joe4]

Both your joins must be the same join type.
See how your top one has an arrow pointing to the second table? That means that you created a Left Outer join. If you double-click on that line, it will show you the Join type.
You need to update the Join type for the second line, so that it matches the first. So double-click on that line and choose the Join type that matches the first (will probably be the second option).

 

[Darkzler]

Hi again Joe4,

Sadly that didn't work, if I did it correctly that is. See the picture with the join types and the actual result. I've edited the second picture and made a red circle with the values that I want to show once.

 

[Joe4]

It is because of the values in your Acc Code, Acc Group N..., and Cost fields differ between those two records.
So, if you want to include those fields in your query, but want to collapse those down to single records, you need to apply an Aggregate Function on those fields, instead of using "Group By".

The way that the query works, is it will combine all records where the combination of the "Group By" fields creates a unique record. So if you "Group By" some fields which are different, you will get multiple records. So you need to either aggregate the differing fields, or leave them out of the query. You would probably use "Sum" on the Cost field. For the other text fields, you might use something like First or Last.

 

[Darkzler]

Ah, that is exactly what I am after. Tho I gave it a try and I obviously got it wrong, do you have any input to the picture attached?

 

[Joe4]

Can you post what it is returning?