Create A Master List with Data from all other tables??

[caseym]

I am currently creating a database that will be used as a template for Product Specifications for Interior Design (Products designers specify will be inputted to create a cut sheet report for the product).



I have the database set up with different tables for each product catergory. (i.e. Lighting, Paint, Wallcovering) etc. Each table currently has a yes/no column linked to a query used to print only selected reports.

To make it easier for the designers to select what they want to print I would like to make a master lsit linked to all of the tables So they can select what they want to print from all the catergories without having to go through all the tables individually. I dont need all fields from the tables, only a few that are mutal (ID, Item Description, and the Print Yes/No Column)

Is what I want to do even possible??

any help will be appreciated!!

[HiTechCoach]

I am currently creating a database that will be used as a template for Product Specifications for Interior Design (Products designers specify will be inputted to create a cut sheet report for the product).

I have the database set up with different tables for each product catergory. (i.e. Lighting, Paint, Wallcovering) etc. Each table currently has a yes/no column linked to a query used to print only selected reports.

To make it easier for the designers to select what they want to print I would like to make a master lsit linked to all of the tables So they can select what they want to print from all the catergories without having to go through all the tables individually. I dont need all fields from the tables, only a few that are mutal (ID, Item Description, and the Print Yes/No Column)

Is what I want to do even possible??

any help will be appreciated!!

The good news is its possible with some work. The bad news is that the fact that you need to do it is a huge RED flag that your database was properly normalized/design.

Using table names as data (in your case the category) is rarely if ever a good design. There is possible a good reason to do it. I just have never seen it in 30+ years as developing database software.

I would urge you to fix you design. You will only continue to have issues if you don't. If you keep your current design there will be things that will be very difficult to do without lots of extra work. As you are already fidning out.

As a bandage you can use a Union query to put all the products back into a single recordset.

You should also want to read this:

Don't use Yes/No fields to store preferences

[ajetrumpet]

Maybe Boyd doesn't know this but...

CASEY. this is at least the 5th post of yours with the SAME title and the SAME content, verbatim that I've seen over the past 2 days on 2 different forums that I look at. That's not cool. Don't do that.

That's both annoying, and a complete waste of other people's time.

[caseym]

Maybe Boyd doesn't know this but...

CASEY. this is at least the 5th post of yours with the SAME title and the SAME content, verbatim that I've seen over the past 2 days on 2 different forums that I look at. That's not cool. Don't do that.

That's both annoying, and a complete waste of other people's time.

Ajetrumpet.. the only other place I have posted this question was on Yahoo Answers where I was advised to post to an Access Forum since its a somewhat complicated question that would need step-by-step help. I've noticed though when googling this trying to find information I saw my Yahoo Post had been posted to other websites.

So its not my fault it's been posted to mulitple places........

[caseym]

The good news is its possible with some work. The bad news is that the fact that you need to do it is a huge RED flag that your database was properly normalized/design.

Using table names as data (in your case the category) is rarely if ever a good design. There is possible a good reason to do it. I just have never seen it in 30+ years as developing database software.

I would urge you to fix you design. You will only continue to have issues if you don't. If you keep your current design there will be things that will be very difficult to do without lots of extra work. As you are already fidning out.

As a bandage you can use a Union query to put all the products back into a single recordset.

You should also want to read this:

Don't use Yes/No fields to store preferences

You know I was thinking the same thing as I was thinking about this last night (Isn't is funy when things suddenly click when you least expect it)

I realized last night I should have put everything in one table, and then instead of using the ID column for the spec code I should've made 2 column for the item code. One column for the item type possibly as a lookup column with the different product types and another for the number. (Our spec numbering goes LT-01, LT-02 etc.). And then I should've used queries to seperate the item types for reports and what not.

This is the first major database I've built like this.. its definitely been a big learning experience

[HiTechCoach]

Ajetrumpet.. the only other place I have posted this question was on Yahoo Answers where I was advised to post to an Access Forum since its a somewhat complicated question that would need step-by-step help. I've noticed though when googling this trying to find information I saw my Yahoo Post had been posted to other websites.

So its not my fault it's been posted to mulitple places........

That is true. Some sites are like newsgroup readers. Showing post from the newsgroups. I think Accessmonster.com is one that does it. I tend to avoid these sites since it can be very comfusing to see the same post over and over.

[HiTechCoach]

You know I was thinking the same thing as I was thinking about this last night (Isn't is funy when things suddenly click when you least expect it)

I realized last night I should have put everything in one table, and then instead of using the ID column for the spec code I should've made 2 column for the item code. One column for the item type possibly as a lookup column with the different product types and another for the number. (Our spec numbering goes LT-01, LT-02 etc.). And then I should've used queries to seperate the item types for reports and what not.

This is the first major database I've built like this.. its definitely been a big learning experience

Sounds like you are getting the idea of data normalization.

Rarely does a single table look anything like a form or report.

Form are for data entry. You might event use multiple forms to enter data into the same table. Forms commonly use multiple tables by using sub forms, combo boxes, list boxes, etc.

Queries allow you to combine data from multiple related tables and.or filter data as need for reports.

Reports generally take the data and repackage it. You might have just summary data with all the detail like you would have on a data entry form.

Forms and reports rarely look the same.

Remember that tables are design to properly store the data. Resist the urge to let form and report desdigns have to much influence the table design.

[caseym]

Sounds like you are getting the idea of data normalization.

Rarely does a single table look anything like a form or report.

Form are for data entry. You might event use multiple forms to enter data into the same table. Forms commonly use multiple tables by using sub forms, combo boxes, list boxes, etc.

Queries allow you to combine data from multiple related tables and.or filter data as need for reports.

Reports generally take the data and repackage it. You might have just summary data with all the detail like you would have on a data entry form.

Forms and reports rarely look the same.

Remember that tables are design to properly store the data. Resist the urge to let form and report desdigns have to much influence the table design.

Thank you so much for the help/advice!

[HiTechCoach]

Thank you so much for the help/advice!

You're welcome.

Glad we could assist.

[ellixer]

Maybe Boyd doesn't know this but...

CASEY. this is at least the 5th post of yours with the SAME title and the SAME content, verbatim that I've seen over the past 2 days on 2 different forums that I look at. That's not cool. Don't do that.

That's both annoying, and a complete waste of other people's time.

Just so you know, you are an arse in most of your responses. Thats not cool. Dont do that.