Dynamic SQL Query

[jontout]

I've had a random thought that I need some clarification on please.

I'm used to writing SQL SELECT queries in Access 2003 that takes part of the SELECT criteria from either textboxes or combo boxes, and wondered if it was possible to also make the table dynamic?

SELECT FirstName, Surname
FROM cboTable
WHERE Surname = txtSurname.value

Where cboTable is a list of 5 available tables for 5 different departments?

I can do this code within Access VBA, I'm more curious as to wether it's 'doable' in Access.

Cheers,

Jon

 

[SkipVought]

Hi,

" cboTable is a list of 5 available tables for 5 different departments"

This is generally NOT a best and accepted design for databased.

Similar data (tables for 5 different departments) ought to be in ONE table with one field for departmentID.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[jontout]

Sorry, I should clarify that cboTable is a combo box with departments that link to specific tables that already exist.
Rather than have 5 queries (for each department), I'm wondering if I can have a generic query (stored in Access)?

 

[lespaul]

We are assuming that you want to put a list in the combo box with departments:
[tt]
Information Technology
Human Resources
Purchasing
Accounting
Customer Service[/tt]

so you could write a query like:
[tt]FROM [Information Technology][/tt]
or
[tt]FROM [Human Resources][/tt]

Skip's point was that you should NOT have a table for each department. You should have a SINGLE table with an identifier of DEPT.


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

I generally agree with one table for all departments with a field that identifies the department. If this is not possible, you might consider creating a union query of the 5 separate tables so they become one:

SELECT FirstName, SurName, "HR" as Dept
FROM tblHR
UNION ALL
SELECT FirstName, SurName, "IT"
FROM tblIT
UNION ALL
SELECT FirstName, SurName, "Eng"
FROM tblEng
UNION ALL
SELECT FirstName, SurName, "Finance"
FROM tblFinance;

You can then select the Dept from a combo box.




Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

While I whole heartedly agree that you should have one table, there is another way...

You can build your SQL in code by concatenating the table name in.

Then depending on what you want to do with it you can set that built sql string on either the SQL property of a querydef object (a query) or the recordsource of a form.

The catch here is that the query will never become complied and will run slower. Sometimes building SQL in code is necessary. Honestly, at this point I would probably have 4 different queries and have the combo select the query name instead.

If necessary, you might right code to generate your queries and write their names into the table behind the combobox.

All this is somewhat involved coding. And it could all be avoided with having all the data in one table.

If you need more help post back.

 

[jontout]

The catch here is that the query will never become complied and will run slower. Sometimes building SQL in code is necessary. Honestly, at this point I would probably have 4 different queries and have the combo select the query name instead.

Cheers, Lameid - that's the point that I'm currently at, an identical query replicated 4 times for 4 different departments. At some point I've got to modify the queries to pick up a username, which will mean 8 identicals.
I thought that his looked 'scruffy', but if it's 'Best Practice', it's that way for a reason.

Thanks all for your input.

Cheers,

Jon

 

[lameid]

In most programming there are two or three ways to go. Performance, manageablity or sometimes lazy. I try to avoid lazy wherever possible but the other 2 have their merits.

That said, you might consider a code solution if you are forseeing a huge problem.