query problem

acoon

New member
Local time
Today, 10:39
Joined
Dec 25, 2007
Messages
8
I have a table named Voucher with 3 field:
voucherCode (every voucher has a unique code)
ordernum (there can be many vouchers with the same ordernum)
vouchertype

The query that I cant manage to build is a query that returns a table that each row contains 3 field:
ordernum (grouped by)
voucherCode (the lowest for each ordernum)
vouchertype (the type of the voucherCode displays)
 
Can you explain a little more. Where are you running into problems? What are the expected results? what results are you currently getting?
 
I think the easiest way may be to build three queries, one for each field and then join these together in a fourth query.
 
Can you explain a little more. Where are you running into problems? What are the expected results? what results are you currently getting?


well...

I managed to write the following query:

SELECT Voucher.ordernum, Min(Voucher .voucherCode) AS Minimum
FROM Voucher
GROUP BY Voucher .ordernum;

It creats a table with two fields:
the first-ordernum-the orders numbers
the second-Minimum-the lowest voucherCode for each order num.

My problem is that I am not able to add a third field-the vouchertype.
it is because every order has more than one Voucher and therefore more than one vouchertype.
I want it to dispaly only the voucher type of the voucher with the lowest code (for each order).

for example:
If I have only two orders with two vouchers each the voucher table will look like:

voucherCode |ordernum|vouchertype
111 |1 |typeA
222 |1 |typeB
321 |2 |Type A
123 |2 |typeC

The query I managed to build creates the following table:

ordernum|voucherCode
1|111
2|123

The query I want to build has to show the following table:

ordernum|voucherCode |vouchertype
1 |111 |typeA
2 |123 |typeC
 
I have tried something and it works.

first I wrote the first query (called q1):

SELECT voucher.ordernum, Min(voucher.vouchercode) AS Minimum
FROM voucher
GROUP BY voucher.ordernum;

And I used it in a second query:

SELECT DISTINCT q1.ordernum, q1.Minimum, voucher.vouchertype
FROM voucher RIGHT JOIN q1 ON voucher.ordernum = q1.ordernum;
 

Users who are viewing this thread

Back
Top Bottom