Yet another question but i promise that it will be the last.....for a week

[ludakrishna420]

1 query for multiple tables.

So i have just completed my database and everything works fine. Now, my managers wants me to go back and do it again for other internal companies. Mind you that we have over 100+ and i am not in the mood of going through and doing something that is not my job. Anyways, all of the companies have the same names for columns, tables and files. for example, 123table for company 123, 124table for company 124 and so forth. This is how it is for every single company. Since all of the data is in one Database, i was wondering if i could write a code or something which in the beginning will ask what company "you are" and what company you want to "dealing with"

For me, my company will be 123 and i will be dealing with 456. So when i enter this in the beginning, it will auto populate the info. In other words, I want to name everything in the query "*table" and that * to be filled by the company number you enter. I was wondering if this was at all possible.

 

[CraigDolphin]

Um. Do you mean to say that you want to create a new database for each of these 'internal companies' with slightly renamed tables, fields etc? If so, why not create one db with 'generic' table/field names etc, and then make copies of the blank db for each internal company to use (different db file name for each internal company).

That said, why didn't you just create one database with a field in the tables to identify the internal company the record belongs to? Then all the data is in one place and only one file to manage.

 

[Rabbie]

Can you please use a title that indicates what the post is about. Then people who are searching this forum mayn find what they are looking for

 

[ludakrishna420]

Um. Do you mean to say that you want to create a new database for each of these 'internal companies' with slightly renamed tables, fields etc? If so, why not create one db with 'generic' table/field names etc, and then make copies of the blank db for each internal company to use (different db file name for each internal company).

That said, why didn't you just create one database with a field in the tables to identify the internal company the record belongs to? Then all the data is in one place and only one file to manage.

Actually, i already have a database with all those tables in it already. They are already named and the only difference between each of them is the first three number. Besides that, they are identical down to the column headers. I just wanted to create a query which will ask for the first the number before the start and then go and use that table. I was thinking something along the lines of a TI calculator program.

Input a, A
y=A+2
(we can add anything for A but the answer will always be A=2). I wanted something like this.

Input a, A
Atablename
(The A would be the first 3 letters of the company)

 

[Rich]

Are you saying that you have a table for each company?

 

[CraigDolphin]

If you created a field to identify the internal company each record in each table belonged to, you would be able to query using internal company ID as a criterion exactly as you want.

Unless I misunderstand you, I strongly suggest you read up on database normalization. You shouldn't have multiple identical tables that differ only in the table and field names. Think one table, with one additional field in that table as an identifier as to which internal company ### the record belongs to.

 

[ludakrishna420]

If you created a field to identify the internal company each record in each table belonged to, you would be able to query using internal company ID as a criterion exactly as you want.

Unless I misunderstand you, I strongly suggest you read up on database normalization. You shouldn't have multiple identical tables that differ only in the table and field names. Think one table, with one additional field in that table as an identifier as to which internal company ### the record belongs to.

That is exactly what i want to do but at a company like Boeing, change is never welcomed easily.

 

[CraigDolphin]

at a company like Boeing, change is never welcomed easily.

As I recall, Boeing's business depends on something that was revolutionary in its day: powered flight. If Boeing is now opposed to change that doesn't bode well for its future IMO.

Back to the topic.

I hope Doc_Man weighs in on this because he works for the US government and if you want to discuss institutional inertia and db design he's the man.

That said, and with the strongest of reservations, if you have to continue with that horrible design then you need to use vba to make your queries dynamically.

Essentially, you make the user pick an 'internal company' ID in a combo box, or somesuch, and in the after update event of the combo box you might put in some code akin to the following:

Dim strSQL as string


strSQL = "Select ....whatever " & _
             "From " & ME.ComboBoxName & "table " & _
             "Where ...whatever ;" 

docmd.runsql strSQL

Now, you'll have to edit the sql as necessary, perhaps even make it into a maketable query so you can create a dynamic table that can be hardwired to your forms. You could also use the dynamically created sql as the source for a recordset to do more operations on in vba.

I don't envy you.