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

Darkzler

New Member
Joined
Sep 25, 2015
Messages
17
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
 

Attachments

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 25, 2015
Messages
17
Hi Joe4,

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

Attachments

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 25, 2015
Messages
17
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.
 

Attachments

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 25, 2015
Messages
17
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?
 

Attachments

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,042
Office Version
365
Platform
Windows
Can you post what it is returning?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,727
Messages
5,446,162
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top