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
Hi Joe4,

Sorry for a late reply, I've attached the result out of the statements above.
 

Attachments

  • Capture9.JPG
    Capture9.JPG
    131.2 KB · Views: 6
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
So based on your recent attachment, can you explain what you want combined that isn't already combined?
Note that all your Transport numbers listed are unique. So if you wanted to collapse your data down anymore, you would need to either aggregate that column also, or remove it from the query.
 
Upvote 0
I'll try to explain, my key column is "Transport", within each transport I have a specific amount of shipments, payweight and cost.

The cost is divided into account groups, like 50, 51 etc. Each transport can have cost from several customers, on the same account groups. Instead of having the CON/Payweight to show multiple times I want it only once per unique transport. For example, if there is 10 shipments and three rows, I want to see the amount 10 and not 10x3.

I have submitted two pictures, one is the result of the database where I have removed account groups. The second is the raw data. I can see the transport having 5 different customers but in the access report I only see 1 but the total amount (cost) is correct.
 

Attachments

  • Bild1.jpg
    Bild1.jpg
    181.1 KB · Views: 4
  • Capture11.JPG
    Capture11.JPG
    61 KB · Views: 3
Upvote 0
I changed the type of total to Group by and removed the account number/group, then it worked, see picture #1.

However, how can I add the different and still get the same result in Cons/Payweight and not counting con*amount of rows per customer?
 

Attachments

  • Capture12.JPG
    Capture12.JPG
    65.8 KB · Views: 2
Upvote 0
I am sorry, I am looking at your images and your descriptions, and am still very confused as to what you are saying.

So let me go about this a different, and explain to your how aggregate queries work. In an Aggregate Query, you have to do one of two with each field you are listing in the query:
1. It can be "Grouped By"
2. It can "Aggregated"

For fields that you elect to "Group By", it will only group records for which ALL the grouped by fields are the same.
For example, if you are grouping by Field1 and Field2, and your original data looks like this:

Table3

Field1Field2
AB
AB
AC
XY
XZ
XZ
"Grouping" by those two fields will look like this:

Query5

Field1Field2
AB
AC
XY
XZ
So, it collapses down as far as it can, where you have "unique" combinations, when all your "Grouped" fields are taken into account.

If we wanted to see only one "A" record and one "X" record, we would either need to change Field2 to an aggregate field (and maybe take the First instance of it), or we would need to remove it from the query altogether.

So, in your example, it cannot be collapsed down any further because you are grouping by Customer, and all the Customer values left are unique, so they cannot be collapsed down any further.

If you wanted something like a "Transport" total, and show that on each customer record, you would first need to do an Aggregate Query, when you are only Grouping on the Transport number, and summing the Cost, and then link that query back to your original table to show the Transport total with each customer record.
 
Upvote 0
Wow, thanks for a very thorough explanation Joe4! Greatly appriciated.

I've read it through two times and I grasp pretty much everything but the last sentence. However, I think I've managed to get a result that is ok for me. I removed the account group and code which gave me only duplicate values on shipments/payweight where there are several different customers which is fine by me.

Please see the attached image, how would I then do as you suggest in your last sentence to link back to my original table?
 

Attachments

  • Capture13.JPG
    Capture13.JPG
    100 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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