Solved

Microsoft Access 2010 Return lowest weight value from table using code

Posted on 2013-12-01
14
474 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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