Solved

Microsoft Access 2010 Return lowest weight value from table using code

Posted on 2013-12-01
14
463 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 500 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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