Multiple Tables Joined ????

[Ekim]

I am about to create an Access Database for a company that Has a few diverse product lines.



I want to have a different table/form for each type of product

Example

tblArtGlass

tblCoatings

tblBricks

I want to be able to create elaborate Pricing engines and track other product specific details with completely different Tables/Forms.

When Creating work orders or placing orders,
I want to be able to see and choose from all the Separate product tables but be treated as a single product listing.


Will a Union Query do what I need?
How do I link it all together with primary keys.

[June7]

Will these 3 product tables all have the same fields? If so, should just be one table with another field for ProductType (values of Glass, Coatings, Bricks). Yes, a UNION query could be used to combine the 3 tables into the one structure, so why not just start with that table anyway. UNION queries cause trouble in VBA. Opening a recordset from a UNION query or any query based on a UNION query will not work (or so I experienced). Having to resort to UNION query is an indication of bad design. It is easier to 'split' a table by use of filters.

[Ekim]

The 3 tables will have some of the same fields like SKU, Item, Wholesale Price, Retail Price etc...
I want to have 3 completely different interfaces for developing and managing different product types. But I want all the Basic Product types to be merged into one list of products when it comes to placing orders etc.

They will have several other fields that are product specific.

As I explore Union Query..
It seems like a nifty way to see a list of something, but not a very good way of integrating and tracking data.

Thanks for your time...

[dblife]

i have to agree with jun7 here.
much easier to filter data later than try to join badly structured data.
a product is a product.
my advice?
make a list of the attributes every product has, such as part number, weight, colour - you get the idea.
this is your product table.
if one product line is say, engines and another product line is books and you want to record engine size for those engines but also isbn number for books then it might be time to create sub tables with those unique attributes as 1-1 relationships with the main record.
a sub table holding book details would obviously only relate to books in the main table and a sub table for engines would only have related records for engines in the main table.
not sure how easy that is to understand - let me know if you need further explanation.

[Ekim]

Thanks dblife,

Yea, I understand doing the sub tables...
Thats what I wound up doing...
I was just hoping for some other slick way to hook stuff up.

Part of my quest also has to do with building a quote engine.

Where I can take different Tables/Forms and build assemblies/quotes Line item style with a sub form.
If only I could build a way to choose from several form/tables on each line item of a sub form in datasheet view.

That would allow me to bid fairly complicated jobs easily.

Thanks,
Ekim

[dblife]

there is another approach.
you would have the same main product table.
a table for attribute names... enginecc, isbn, pinsize, roughnessindex - all obscure stuff in one table.
you would then bring these two tables together in a junction tables with the product and attribute(s) applicable to that product.
another table would allow you record a value aginst a specific product/attribute.
complicated i know.
if it is not clear, let me know and i will knock up a quick example in a2003.

[Ekim]

Thanks dblife

I understand Junction tables a bit.

A table between two tables with a joined Primary Key.

Ive used a Junction table as a 1 to Many before..
For instance tblCompany having many tblEmployees

I was actually liking what you had said earlier about putting all the obscure info on another table in a 1to1.

Thanks for your thoughts on this. It is very helpful.
I have a small start on this project.
I want to layout the logic a bit tighter before this thing gets to deep in the rabbit hole.

[dblife]

youre welcome.
the main reason for junction tables is to handle many to many relationships.
the first approach will allow well formatted forms and reports.
the second approach will give you flexibility but the forms and reports will come out looking like lists and not look as slick.
let me know if there's anything else i can help with.

[Ekim]

Yes.. I know what you mean about the reports DBLife... That was sorta why I was looking for a different way to organize the tables... I may try to master some code to past data into other fields for formatting reports....

Have you ever used crystal reports?
Would a program like that give you more reporting options?

[dblife]

I know of crystal reports but i have never used it.
Cannot help you there.
Sorry.

[June7]

Never programmed with Crystal Reports. Worked with other apps that have incorporated it, like a Visual Basic coded app. Costs about $500+.