asked on Microsoft Access 2010 Return lowest weight value from table using code
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.
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.
Microsoft Access
Last Comment
marlind605
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
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
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
right-click the query builder window --> query type --> Make Table Query
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
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
ASKER
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.
Did you try my query at http:#a39688969 (You have two possible solutions here) ?
<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 ?
yes that is correct.
what is not clear to you ?
ASKER
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.
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.
ASKER
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.
ASKER
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.
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.
post the sql of the query you used..
which one do you want to show in case of this tie in weight?
which one do you want to show in case of this tie in weight?
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the help.
Open in new window