Need some help

[oaktree68]

I may be in the wrong forum, and if so, I apologize. If there was a similar example, I'm happy to search if someone could give me my search term.

Anyway, heres what I'm trying to do

I have 1 database table with a bunch of items in it (in the fourth field)

The second database table has a list of products that contain the parts in them

So the example would be:

Table 1
Part
a1
a2
a3
a4
b1
b2
b3
b4

Table Two (look at it as finished product contains parts....)

Product1 a1 a4 b4
Product2 b3 a2 b1

and so on and so on.

What I want to be able to do is create a 3rd table, or query...report, anything that will link the products together, so a finished report would look like this:

a1 Product1
a2 Product2
a3
a4 Product1
b1 Product2
b2
b3 Product2
b4 Product1

Any help in getting me started with this would be appreciative.

Thank you,
George

 

[jjturner]

Hello and Welcome!

First point, perhaps you're already realizing - you don't want to store the data according to how your Table Two is setup.

To convert what you have stored already you can create a UNION query:

SELECT PartField1 AS PartCode, Product FROM [Table Two]

UNION SELECT PartField2, Product FROM [Table Two]

UNION SELECT PartField3, Product FROM [Table Two];

Once you have that, you can create a Make-Table query based on this query, or paste the results of this query into a corresponding table structure.

HTH,
John

 

[oaktree68]

Well, I made my first union table (not too bad), but I'm getting a pop up box asking me to type in a Panel Id (parameter value). Is there a way to kick out this report while getting all the Panel Id's on 1 query?

Here is what I have:

SELECT J1 AS Module, Panel_ID FROM Panels

UNION SELECT J2, Panel_ID FROM Panels

UNION SELECT J3, Panel_ID FROM Panels;


Thank you again for any additional help

George

 

[jjturner]

It should not be prompting you for a parameter value.

Is "Panel_ID" the name of your 'Product' field and is it spelled correctly everywhere it occurs in the query?

 

[oaktree68]

Sigh....I'm an idot. Now on to the next part. Probably be back soon

Thanks,
George

 

[jjturner]

NP - and just to mention, when you condense all your Modules into a single column, it usually helps to add a corresponding column "ModuleType", so you might want to consider adding that column to the new table structure.

HTH,
John

 

[oaktree68]

Good idea. I think I am going to pull from another source with alot of that information in it. My next question, can I do this again and again with multiple tables? Say I have 10 different panel databases (hoping not to do that), but can I merge all of them into the same sheet? Or did I jsut make things 20x harder?

thanks again,

George

 

[jjturner]

Depends...
I'm assuming you're referring to panel 'tables' rather than 'databases'. If each table has the same structure and same # of fields, and each of them have the same field names, then it would probably make sense to create a User-Defined Function (UDF) in VBA.

But if the table structures vary, then you would probably have to manually pore through the tables to see what fields can be combined into the "master" table, and then manually write the SQL statement, as you've already done.

The basic idea is that if you find there are multiple tables with the same structure and same type of data, then all those records really belong in a single table. Then of course, you would eliminate the redundant tables.

HTH,
John