0

I have several tables with the same data structure (they're filled with a bunch of stuff, in separate .accdb files to account for the 2GB limit) and need to retrieve info from one of them based on a field in a form.

Upon researching I came up with the following, but it won't seem to work.

SELECT MyNumber, MyName, MyPage, MyDrawing
FROM Switch([Forms]![View_Info]![Contract] = "Contract1", "tblContract1", [Forms]![View_Info]![Contract] = "Contract2", "tblContract2")
WHERE (MyNumber = [Forms]![View_Info]![MyNumber])

Syntax error in FROM clause. In this example I only used 4 fields and 2 tables but in fact there are around 9 tables and 20 fields in each that I wish to retrieve.

Can someone shed some light on this? I have a really hard time with SQL, so I apologize if this is quite basic.

Thanks in advance, Rafael.

Improve this question
9
  • 2
    SWITCH() is a function that returns a scalar value, typically used in a SELECT clause. Commented Jun 26, 2019 at 16:23
  • 2
    That's not exactly the real issue. In MS Access, functions are not allowed in FROM and JOIN that refer to tables. Commented Jun 26, 2019 at 16:28
  • 2
    By the way, consider a database redesign. Even with MS Access' individual file limit, normalization should be exercised. Having numbered tables like tblContract1, tblContract2, ... is not optimal setup. Keep ALL contracts in one database, other items in other databases and link as needed. Commented Jun 26, 2019 at 16:29
  • 1
    You'd be surprised how few business logic cannot be resolved with normalization. If you find an example, please share! Plus, storage is more efficient, redundancy and complex queries are avoided, and you can exercise referential integrity (one-to-one, one-to-many relationships) better! Start today with a redesign. Good luck! Commented Jun 26, 2019 at 16:38
  • 1
    @Parfait Ended up doing a redesign, took me a while but things are smoother. Thanks for the tip, would've been up to my knees in s**t real soon :p Commented Jul 4, 2019 at 13:33

2 Answers 2

Reset to default
1

You cannot return the table name from a function in the SQL FROM clause. If your table is determined dynamically, then you must build the SQL command string dynamically.

Dim tableName As String, sql As String

tableName = Switch(...)
sql = "SELECT ... FROM [" & tableName & "] WHERE ..."

As @forpas explains in his answer, you can use a UNION query, but this will always query all the tables. Since the filter is not based on a table column, the filtering will occur on the client side, i.e. in your application.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Oh sorry, I didn't refresh the page for a while. Removed this part.
0

Try this UNION:

SELECT MyNumber, MyName, MyPage, MyDrawing
FROM tblContract1
WHERE (MyNumber = [Forms]![View_Info]![MyNumber]) AND [Forms]![View_Info]![Contract] = "Contract1"
UNION
SELECT MyNumber, MyName, MyPage, MyDrawing
FROM tblContract2
WHERE (MyNumber = [Forms]![View_Info]![MyNumber]) AND [Forms]![View_Info]![Contract] = "Contract2"

Each query of the UNION contains in the WHERE clause the condition:

[Forms]![View_Info]![Contract] = "Contract?"
Improve this answer

2 Comments

Now complete this for 9 contract tables which reside across multiple databases! Ouch... OP would do better with redesign. But this answer shows the complex queries required of separate named tables.
Really, 9? I should have read better the question. Well I'm not the one to write the full code. I hope the OP knows how to write the rest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.