Database Structure, Search many tables

[Gman]

I am new to Access, and have search for many post and have set up my database to some of waht i want but I neeed some help to do make it more complex.

What I have, or will have, is about a 30 tables, each table has the same fields, but has different information. Each tabel is compiled of many records, each has about 6 field.

I have created a form that will allow me to search a one table and I use the criteria in the query form to filter the table based on my keyword. Then I get a report with only the recprds that contain my keyword.

But I would like to be able to add checkbox, each checkbox would be the name of one on the many tables. So I owuld check which tabel i want to search using my keyword, and then get a report with records from all tables I checked, but filter them based on my keyword.

I currently do this by entering a keyword in a textbox in my form, and then use a command button to open a report. I set up a query with the criteria set to the path of the textbox.

So i am not sur if this would be the correct database setup, and how could I do this.

Thanks Gman

 

[Banana]

First, I'm a bit concerned that this is not correctly normalized because in most cases, you wouldn't need to repeat the fields across 30 tables. If you're sure you know database normalization, you can ignore that.

To answer your question, you would need to use a combination of a query against MSysObject table to get you the names of tables and a dynamic SQL.

This is a great site, and has an example on how to get the names of table in a query

You could just use a listbox and enable multi-select (there's a sample database in the forum titled "Sample Databases"; go and take look or search the forum- the search button is on the blue navigation bar).

Once you've made the selection, you probably would use a button to run the code to create a dynamic SQL where you insert the table name and the field to return the recordset you want.

HTH.

 

[Gman]

I not familiar with normalization, but I have updated my database,

I have a table, a query that searches that table base on two filters.
I open my form, select the two filters (system & subsystem). Then I open a report that is based on the query. I want to be able, when i finish, to be able to filter the query by any combination of the about 5 filters. So far I have it working with two, so I know i can get it to work with more.

Now, what I cannot figure out is, on my form I have a multiselect combo box, each name in the box is a table name, (not all set up yet). Rigth now I search a single table, but I want to be able to select any table in the list, it could any combination of tables.

Now, all my tables have the same fields, I want to keep them seperate, cause when I finish this, I will have 1000' of records with in each table, and need to keep seperate. I guess I could append all tables when I have complete the data entry, but could any help.

Is there a way when I select the table names, to somehow search all tables and have my report be filtered. Is there a way to create a master table each based on my selections each time I run the report.

I have attached my file,

Thanks

 

[georgedwilkinson]

In answer to your first post: you are not doing it right. Same applies for your second post. You need to normalize your design and store "like" data in the same place.

You did not attach any files, but I won't be able to look at your file. You've already said enough to answer your question and the problems that will arise: your approach is wrong. Normalize and objectify.

 

[stopher]

Now, all my tables have the same fields, I want to keep them seperate, cause when I finish this, I will have 1000' of records with in each table, and need to keep seperate. I guess I could append all tables when I have complete the data entry, but could any help.

I'm going to say the same as George and Banana. If you normalise you won't have the problem you are trying to deal with. Normalisation is so important.

If the data is the same structure then put it all in one table. You can make sure it is differentiated by adding an extra field where you can enter a key as to where the data came from. Then it becomes easy to filter on this key. The fact that there are thousands of records is pretty irrelevant.

Chris

 

[Gman]

Thanks guys,

If you think that is the best approach, then I will combine when I enter all info,