New to Access: Query Based on TempVar?

[Stang70Fastback]

Hey guys. I'll start by saying I know next-to-nothing about Access, but I've managed to build myself a decent little program by muddling along. I've gotten to a point where I need some help! I currently have this as a query in my file:

SELECT *
FROM [Shift List] LEFT JOIN RUNSHEET ON left([Shift List].[Shift Description],4) = RUNSHEET.Route;

You can see that I am joining two tables: Shift List and RUNSHEET.

As it turns out, there are different versions of RUNSHEET depending on the day of the week, and what sort of service we are providing. I have linked these as "Weekdays", "Saturdays", "Sundays", etc...

My question is, is it possible to alter the code above, and to replace 'RUNSHEET' with a variable name that I could set based on which table I wanted it to be joined with? So the resulting code would look something like:

SELECT *
FROM [Shift List] LEFT JOIN MYVARIABLE ON left([Shift List].[Shift Description],4) = MYVARIABLE.Route;

Where MYVARIABLE would be either 'Weekdays', 'Saturdays', etc...

Does that question make sense? I don't even know if this is possible. I assume Access performs those queries only when needed, so I would assume as long as the variable is set before I make use of any of the queries, it would work? Help!

I read something somewhere about how you need to "drop out" of SQL in order to use variables... and it had to do with using quotes, but I honestly don't know anything about SQL so simple explanations such as those don't get me very far :-/

Thanks guys!

 

[Galaxiom]

The objects in a query cannot be designated by variables. You would have to create the query string dynamically in VBA.

However the real problem is your database structure which is using different tables to hold the same kind of data. These weekday tables should be combined into one table with a field for the dates.

 

[jdraw]

I agree with Galaxiom that your basic issue is database design.

You may wish to research Normalization.
There is a link to Normalization within this article on Database Principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Good luck with your project

 

[Stang70Fastback]

Unfortunately, this is an awkward scenario. I cannot change the structure of our database, as we NEED to have the various, SEPARATE tables for a variety of reasons.

I appreciate your replies. I ended up creating, essentially, several reports that are each linked to an individual table. Makes things a bit... bulky... but the user doesn't see any of it and it works, so I suppose I'll deal with updating three form designs every time we want to change something. Bah!