How to search through tables for a value?

[hagamablabla]

Hello. I have a problem right now: I have multiple projects that each use different parts. These parts each have a part ID. I have each project in a separate table, and a table with my ID as a primary key and the rest of the part data corresponding to that ID. What I would like to do now is find what projects use a certain part (ID number). My plan is to search through each project's table for that specific ID. If the part ID is found in that project's table, the table's name is appended to a cell. I put this in programming because I'm assuming I need SQL to do all of this, but if there is a built-in way to see where a specific primary key is used, I would definitely prefer to use that.

[June7]

Should not have a table for each project, should be one table.

Options for dealing with this structure:

1. VBA to search through each table

2. emulate single table structure with a UNION query that combines the project tables then search the UNION query, however, there is a limit of 50 SELECT lines in a UNION

[hagamablabla]

Why would it be a bad idea to have a separate table for each project? I'm still in the testing phase of using Access, should I try moving everything onto one table?

[ssanfu]

Doesn't sound like a good design..... Should have a project table that has all projects.


However... how many tables are you talking about.

With 3 - 5 tables, you could write a query for each table, then create a union query to search all at once.
With more tables, I would use VBA, substituting or looping each table name into a query. Print to the immediate window, append to a table, write to a text file,...

[hagamablabla]

However... how many tables are you talking about.

I'm still testing so only a few, but the goal is to have much more than 50.

[ssanfu]

The maintenance would be horrible!

Do you have queries, forms and reports for EACH project????



How do you feel about posting your (blank) dB so we can see the structure?

[hagamablabla]

I was under the impression that I could do a lot more with multi-table queries, forms, and reports, but I guess not. I'm still very new to this and trying to figure out the limits of Access.

I can't seem to upload the database for some reason. Each project does have the same layout though. It has a foreign key column that matches to the IDs.

[ssanfu]

Look at the bottom of June's post (Post #2) on how to attach attachments....

[June7]

Bad because have to modify database design every time you add a new project. This would mean changing queries, forms, reports, code.

Seems to me there should be at least 3 tables.

tblProjects

tblParts

tblProjectParts

[orange]

I agree with June and Steve that your design is poor for the task you have described.
It seems you are new to Access and to database.

Here are 2 recommendations:

For database generally, there are concepts and proven techniques. Here is a link to a post with info on database concepts and planning.

Here is link to a sample database with a search to look for a value(s) in any field any table, it may be applicable.

Good luck.

It would be helpful to readers if you post a copy of your evolving database.(see June's signature for posting/attaching info)

[hagamablabla]

Hello everyone, sorry for the late reply. Based on June's suggestions, I've made a sample database. Is this what I'm supposed to do?

Also, I didn't realize that there was a size limit on attachments. My actual database was way over. Thanks for the help.

[June7]

Looks like a good start. Advise not to use spaces in names for anything. An underscore would be acceptable but no other punctuation or special characters.

[ssanfu]

You might want to see
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.
Do not use reserved words as object names



Note: "Description" (and "Desc") are reserved words
Better would be "ItemDescription" or "ItemDesc"
"ProjectDescription" or "ProjectDesc"

[hagamablabla]

Great, thank you all very much. I'll give the documents a read-through too.

[hagamablabla]

You might want to see
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

I have a feeling you're trying to tell me something here

If I have an existing list of unique IDs, I should still use an autonumber primary key for sorting purposes? And if so, is there a way to make sure that list of IDs still stays unique?