Query Question 2

[eatwork]

DB: MS Access
Query In: MS Access
Problem:
I have two tables, tbl_equipment, tbl_trailercomb. Each item in tbl_equipment can be used like lego to assemble one record in the tbl_trailerComb table. Not quite sure how to accomplish this. Thank you

eg.
tbl_equipment tbl_trailerComb
equipmentId trailerCombId
equipArriveDate
equipEndDate

eg.
tbl_equipment
01-01 01-02 01-03
01/01/2006 01/01/2006 01/01/2006

tbl_trailerComb
01-01,01-02
01-01,01-02,01-03
01-02,01-03

 

[lespaul]

you mean like a Bill of Materials?

do you really have a comma separate list in a single field in tbl_trailerComb?

Have you read the fundamentals document linked below?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hi lespaul,
Thank you for your reply. I know its not the best way to do things, but that field is a string that represents all of the pieces of a trailer. Basically, you can take any piece of equipment and add to it together to form different equipment combinations. The current database does not have the two tables linked at all, but I need them linked to control which items are available when the dates are modified in the equipment table. After modifying that table, combinations that utilize the piece of equipment that is no longer is service can no longer be used. However, these records cannot be deleted because it is important data that the company needs to be able to go back and review in the future.

 

[lespaul]

Basically, you can take any piece of equipment and add to it together to form different equipment combinations.

so you DO mean like a bill of materials.

Let's say I build gadgets. Each gadget is made up of:

1 widget
3 screws
5 plates of metal


So in my ITEMS table I have:

ID Name
1 widget
2 screws
3 metal plates
4 gadget

in my BOM table I have:

MakeID ComponentID Qty
4 1 1 (make a 4 (gadget) using 1 - 1s (widget))
4 2 3 (make a 4 using 3 - 2s (screws))
4 3 5 (make a 5 using 5 - 3s (metal plates)

Somewhere there is a process that "builds" the gadgets to put in my items table. It has to deduct the inventory from the widget, screws and metal plates and add to the inventory the number of gadgets I've made. So if I tell the program that I've made 5 gadgets, it will add 5 gadgets to inventory and substract 5 widgets, 15 screws and 25 metal plates.

Again, have you read the fundamentals document below? have you identified your entities? Is your table in 3NF?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[eatwork]

Hi lespaul,
Thank you for your reply. I think my explanation isn't quite clear because the idea is somewhat similar but is not quite the same as you have described.

The situation is this. I have a trainset. Each boxcar is uniquely numbered, eg. there are three cabooses (01-01,01-02,01-03), there are 5 grain cars(02-01, 02-02, 02-03, 02-04, 02-05), there are 2 cargo boxes (03-01,03-02).

The user will select the different train components needed for a particular trip. eg 2 grain cars, 2 cargo boxes, 1 caboose, or just one caboose, etc. This combination becomes the primary key in the table. eg 02-03,02-04,03-01-03-02,01-01 (this combination can be anything the user selects)

tbl_Equipment
equipmentId startDate end date
01-01 01/01/2006
01-02 01/01/2006
01-03 01/01/2006
02-01 01/01/2006
02-02 01/01/2006
02-03 01/01/2006
etc

tbl_equipmentCombination
equipmentCombination
02-03,02-04,03-01-03-02,01-01

so basically, what you are trying to tell me is that the tbl_equipmentCombination needs to be a join table. with a unique id specifying the combinationId. I don't think this will work because the combinationId is used as a foreign key in numerous tables.

 

[PHV]

I need them linked
Something like this (SQL code) ?
SELECT C.equipmentCombination, E.equipmentId, E.startDate, E.[end date]
FROM tbl_Equipment AS E, tbl_equipmentCombination AS C
WHERE ',' & C.equipmentCombination & ',' Like '*,' & E.equipmentId & ',*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eatwork]

Hi lespaul and phv thank you for your posts.

lespaul,
I have created the join table between my equipment table and my "trailer combination" table.

I have kept the combination text string as the primary key in the trailer combinatino table and used the equipment unit numbers as the second part of the primary key in the join table. so my tables now look like this

tbl_trailerComb
26-74
26-74,32-65
26-74,34-69

tbl_equipment
equipmentId - PK StartDate End Date
26-74 01/01/2006
32-65 01/01/2006
34-69 01/01/2006 08/31/2006

join_tbl_trailerEquipment
trailerCombId equipmentId
26-74 26-74
26-74,32-65 26-74
26-74,32-65 32-65
26-74,34-69 26-74
26-74,34-69 34-69


I think doing it this way will allow me to keep the current db structure and still allow me to query the tables to find out which items were not in service given a particular date. Does anything look out of whack here? or am I on the road to success? thank you

 

[lespaul]

not knowing all the specifics of your situation, I can't be definite, but this looks better equipped for getting the information you are looking for.

 

[eatwork]

Hi lespaul,
Thats great, thank you and phv for all of your help.