?
Solved

Microsoft Access 2010 Return lowest weight value from table using code

Posted on 2013-12-01
14
Medium Priority
?
481 Views
Last Modified: 2013-12-03
I have a tbltransaction with uniquekey, transactioncode,weight. There may be multiple transaction codes for each uniquekey.
Each transactioncode has a weight. I need to create a new table that shows the uniquekey, transactioncode and the lowest weight for that key.
The final table would containe the uniquekey transactioncode and the weight. The unique key will appear 1 time and sample out put follows.

uniquekey      transactioncode            weight
689291300      1234                  1
689291300      3456                  16
689291300      7638                  3
689291301      4850                  8
689291301      4859                  15

returning values
uniquekey      transactioncode            weight
689291300      1234                  1
689291301      4850                  8
I have tried various queries (Min) etc with mixed and inconsistent results. My table may contain up to 1,000,000 records so I am thinking a nested loop in a visual basic routine but just I haven't been able to figure out the right sequence. Thanks for the help.
0
Comment
Question by:marlind605
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39688969
It's doable with a query.  Try this:

SELECT a.uniquekey, a.transactioncode, (SELECT MIN(b.weight) FROM tbltransaction b WHERE b.uniquekey = a.uniquekey) AS MinWeight 
FROM tbltransaction a

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39688970
try this query

select a.*
from tbltransaction as a
inner join
(select min(b.weight) as minWeight, b.uniquekey
   from  tbltransaction as b
   group by b.uniquekey) as c
on a.uniquekey=c.uniquekey and a.weight=c.minWeight
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39688972
The queries above will return the values you need... you can turn it into a Make Table query by:

right-click the query builder window --> query type --> Make Table Query
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39688980
to create a new table, here is modified veraion of the query


select a.* Into NewTableName
from tbltransaction as a
inner join
(select min(b.weight) as minWeight, b.uniquekey
   from  tbltransaction as b
   group by b.uniquekey) as c
on a.uniquekey=c.uniquekey and a.weight=c.minWeight
0
 

Author Comment

by:marlind605
ID: 39691342
captricorn1, I am checking the results and so far so good. When I tried it with the min query before I got some incorrect output. By using the innerjoin on the weight and the uniquekey is this how I can insure myself of getting my correct output. Can you point me to somewhere so I can understand it. Thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691354
Did you try my query at http:#a39688969 (You have two possible solutions here) ?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39691473
<By using the innerjoin on the weight and the uniquekey is this how I can insure myself of getting my correct output. >

yes that is correct.

what is not clear to you ?
0
 

Author Comment

by:marlind605
ID: 39691541
I have found an issue. How do I catch this in the query
uniquekey      transactioncode            weight
689291300      1234                  1
689291300      3456                  16
689291300      7638                  3
689291300      8712                  3
689291301      4850                  8
689291301      4859                  15

returning values
uniquekey      transactioncode            weight
689291300      1234                  1
689291301      4850                  8
689291300      8712                  3

I do not want the a duplicate unique key in the final value. This happens when the weight is identical.
0
 

Author Comment

by:marlind605
ID: 39691544
I just read your comment. I was surprised when I was using the min function in the query why it wasn't coming up correctly.
0
 

Author Comment

by:marlind605
ID: 39691548
BAD EXAMPLE USE THIS ONE.
I have found an issue. How do I catch this in the query
uniquekey      transactioncode            weight
689291300      1234                  1
689291300      3456                  16
689291300      7638                  1
689291300      8712                  3
689291301      4850                  8
689291301      4859                  15

returning values
uniquekey      transactioncode            weight
689291300      1234                  1
689291301      4850                  8
689291300      8712                  1

I do not want the a duplicate unique key in the final value. This happens when the weight is identical.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39691549
post the sql of the query you used..

which one do you want to show in case of this tie in weight?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39691552
Try this:


SELECT a.uniquekey, a.transactioncode, (SELECT FIRST(b.weight) FROM tbltransaction b WHERE b.uniquekey = a.uniquekey ORDER BY b.Weight) AS MinWeight 
FROM tbltransaction a

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39691565
try this one


select a.*
from tbltransaction as a
inner join
(select min(b.weight) as minWeight, b.uniquekey, min(b.transactioncode) as minTransaction
   from  tbltransaction as b
   group by b.uniquekey) as c
on a.uniquekey=c.uniquekey and a.weight=c.minWeight and a.transactioncode=c.mintransaction


or this

select a.*
from tbltransaction as a
inner join
(select min(b.weight) as minWeight, b.uniquekey, last(b.transactioncode) as lastTransaction
   from  tbltransaction as b
   group by b.uniquekey) as c
on a.uniquekey=c.uniquekey and a.weight=c.minWeight and a.transactioncode=c.lasttransaction
0
 

Author Closing Comment

by:marlind605
ID: 39692432
Thanks for the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question