Union Query Blues 1

[FreshFish]

I've muddled through trying to put together a query that takes info from a table and another query so that I can input a Part# and have in spit back all the vendors and prices in US Dollars.

SELECT [PartName], [Part#], [Vendor], [In US Dollars] AS [Price/Unit]
FROM [Euros to Dollars]
UNION SELECT [PartName], [Part#], [Vendor], [Price/Unit]
FROM [Parts]
WHERE [Currency]="Dollars";

I've gotten this far. This asks for a PartName. I could put in "shoe" and it will list parts as having the name "shoe" and spit the list out at me in no particular order. Needles to say this isn't a useful function. I've also added further fields to the query and it asks for a value for them as well.

How do I get it to ask for a Part# and just spit out that info without asking anything else? Preferably aranged from lowest to highest Price/Unit?

Thanks!

 

[bhoran]

One way I handle union queries is to add all the fields I will need from each of my tables (remember you must use alias' so the names match. Then if I need extra fields from one table not available in other tables I use the Union query as if it is a table and simply link it to other tables using a standard query. It does mean a few more resources being used but I find it more manageable.

So in your case once I have aggregated all the fields in the 2 tables I would leave the union query as it is with no selection criteria. Then I would build a select query over that union query and specify any criteria in that query.

p.s. it is also useful to use 'Union All Select' as Union select ignores duplicates.

Hope that is helpful.

 

[Golom]

You can

PARAMETERS [Enter Part Name] Text;

SELECT [PartName], [Part#], [Vendor], [In US Dollars] AS [Price/Unit]
FROM [Euros to Dollars]

WHERE [PartName] = [Enter Part Name]

UNION 

SELECT [PartName], [Part#], [Vendor], [Price/Unit]
FROM [Parts]
WHERE [Currency]="Dollars"

 AND [PartName] = [Enter Part Name]

ORDER BY 4

bhoran: No you don't need to alias the names in each select. A UNION takes its field names from the first select regardless of what they're called in later ones. You do however have to have the same number of fields in each select.

 

[FreshFish]

Ah! Thank you. This is starting to make sense now. I also had to modify my [Euros to Dollars] as it was passing through [Currency] ="Dollars" and giving me 2 results for those fields. Duh.

Much Obliged

 

[FreshFish]

Alright its working and I have used the same formula for Other queries.
New question is how I would incorporate wildcards into the PARAMETER. So that if someone was searching for a "hydraulic drive" they would only need to input "hydr" and they would get the proper return? Would I insert a Like and a '*' in the WHERE statement somewhere?

 

[Golom]

Add another clause to where ... something like

   AND [PartName] LIKE "'*" & [Enter Part Name] & "*'"

 

[FreshFish]

I used...

WHERE [Currency]="Dollars" AND [PartName] LIKE [Enter Part Name:] & "*"

...and it seems to work alright. There aren't many records in the DB yet though.

 

[Golom]

The difference is that your formulation will find parts that start with [Enter Part Name] while mine will find any part that contains that string even if it doesn't start with it. For example

   [Enter Part Name] = "GEAR"

Your query would find GEAR SHIFT but not SPUR GEAR. Mine would find both.

 

[FreshFish]

Very useful. With the number of parts the DB will eventually have that will save alot of time.

I had to get rid of the ' that was in the "*" wildcards as it wasn't returning anything with them in.

 

[bhoran]

Thanks Golom I didn't know that.