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

Darkzler

New Member
Joined
Sep 25, 2015
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

  • Capture1.JPG
    Capture1.JPG
    76.3 KB · Views: 12
  • Capture2.JPG
    Capture2.JPG
    53.3 KB · Views: 12

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
Hi Joe4,

Thanks for taking the time to reply.

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

Attachments

  • Capture3.JPG
    Capture3.JPG
    79.4 KB · Views: 5
  • Capture4.JPG
    Capture4.JPG
    93.2 KB · Views: 5
Upvote 0
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.
 
Upvote 0
Hi Joe4,

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

Attachments

  • Capture5.JPG
    Capture5.JPG
    90.6 KB · Views: 5
Upvote 0
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).
 
Upvote 0
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

  • Capture6.JPG
    Capture6.JPG
    49.4 KB · Views: 2
  • Capture7.JPG
    Capture7.JPG
    132.1 KB · Views: 2
Upvote 0
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.
 
Upvote 0
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

  • Capture8.JPG
    Capture8.JPG
    70.9 KB · Views: 5
Upvote 0
Can you post what it is returning?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top