SQL 2

[dpaulson]

I have two tables. One is Vendors and the other PurchaseOrder. They both have a common field VendorId. The VendorID in the Vendor table is unique, but there can be many instances of the same VendorID in the PurchaseOrder table. I need to return all the fields of each Vendor (only once) that has a matching entry in the PurchaseOrder table. The PurchaseOrder fields are not important.

My sql is "SELECT * FROM Vendors inner JOIN PurchaseOrder ON Vendors.VendorId = PurchaseOrder.VendorId Where Printed = True AND Cleared = False ORDER By VendorName" which will return all the fields of both tables for each entry that is in the purchaseorder table.
I've looked at Distinct ,but I cannot get all the required fields needed. Any ideas for the correct sql?

David Paulson

 

[gmmastros]

Try this...

SELECT [!]Distinct Vendors.[/!]* 
FROM   Vendors  
       inner JOIN PurchaseOrder 
           ON Vendors.VendorId = PurchaseOrder.VendorId
Where  Printed = True 
       AND Cleared = False 
ORDER By VendorName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dpaulson]

Thanks George. That works perfect.

David Paulson

 

[W3REG]

I have a similar problem, I have two tables. One is Members and the other Log. They both have a common field MemID. The MemID in the Member table is unique, but there can be many instances of the same MemID in the Log table. I need to return all the fields of each Member (only once) that has a matching entry in the Log table but I need to include the Date Field from the Log table for only one instance. If I do not include the Date Field the SQL works fine but with the Date field from Log Table included I get multiple returns for each instance of MemID that occur in the Log table.

Here is the SQL that works without the Log[Date] field:
SELECT DISTINCT Log.Callsign, Log.[Mem#], Log.QSLR, MembersARLHS.[First Name], MembersARLHS.[Last Name], MembersARLHS.Add1, MembersARLHS.City, MembersARLHS.State, MembersARLHS.Zip
FROM Log RIGHT JOIN MembersARLHS ON Log.[Mem#] = MembersARLHS.[Mem#]
WHERE (((Log.[Mem#])<>0) AND ((Log.QSLR)<>0))
ORDER BY Log.[Mem#];

I understand that the Date field is unique in each record and that is why the multiple returns. How can I get around this to just show one record with just one date with each record.
I hope I have been clear, Thanks for any help.
Reg W3REG

 

[HarleyQuinn]

You could use the MAX() or MIN() functions to return either the latest or earliest date.

Is there a particular date (e.g. latest) that you want to return or just any of the dates that are in the DB?

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[W3REG]

Thanks Harley, The earliest date would be ok. I am asuming this would be used in the QBE grid i.e.;
Expr1:Min([Log.Date]) Am I on the right track?

Reg W3REG

 

[HarleyQuinn]

Pretty much, I've not done queries in Access for a while but from memory you will need to click the totals button so it groups by the fields and then change the group by in the Date field to MIN.

If you want to do it with the SQL behind just add a MIN([Log.Date]) and group by the other columns in your select.
That should also allow you to exclude the DISTINCT keyword at the start of your select as the fields will now be grouped.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[W3REG]

Thanks Harley that worked. I'll cut and past the SQL into my VB6 code in my application to create my list. Thanks again.

Reg W3REG

 

[HarleyQuinn]

You're welcome, glad I could help.

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.