Solved

Microsoft Access 2010 Return lowest weight value from table using code

Posted on 2013-12-01
14
445 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

786 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