switch or sql select in access

[mikeo1313]

Access doesn't support sql select but I also didn't run across an example where switch was used for anything other then setting string values.

Can I do this:

select
order.[amount], order.[date],
switch (order.[co type]=1, tblcustomers.[co name] as company name,
tblcustomers.[co email] as company email,
order.[co type]=2, tblsuppliers.[co name] as company name,
tblsuppliers.[co email] as company email

from
order, tblcustomers, tblsuppliers

where
order.do = true


Otherwise I ran across this link to do sql selects in Access VBA, I'm not knowledgeable enough to determine whether that code will work out for me or just store string values or not be able to check for more then just 1 group of values.

 

[Galaxiom]

SELECT
order.[amount], order.[date], tblcustomers.[co email], tblcustomers.[co name] as [company name]
FROM
order INNER JOIN tblcustomers ON order.customerID = tblcustomers.ID
WHERE order.[co type]=1
UNION ALL
order.[amount], order.[date], tblsuppliers.[co email], tblsuppliers.[co name] as [company name] 
FROM
order INNER JOIN tblcustomers ON order.supplierID = tblsuppliers.ID
WHERE order.[co type]=2

 

[mikeo1313]

Thank you!

 

[mikeo1313]

I changed up the arrangement of my database after thinking things.

Instead of having separate tables for customers, suppliers and etc I made one table called tblCos, for simplicity.

Whats wrong with this statement as I'd been pretty careful putting it together with excel cell functions. There are 11 fields in tblloads that link to tblCos.

I get run-time error 2342 A runsql action requires an argument consisting of an sql statement.

My statement is intended to make a tblmammoth with INTO before FROM.

 

[mikeo1313]

edit: will make another post for this

 

[mikeo1313]

though I originally didn't inted to do an action query with inner joins,,, I eventually figured out I had to use a several update selects since I reused a table many times in 1 table.

thankfully thats over