Joining multiple categories.

[Bmo]

We build components into assemblies I would like to provide an assembly/component schedule to the people responsible for ordering parts.

I have 3 tables.

Table 1 is the schedule.
Table 2 is the part list with schedule information.
Table 3 is the category info.

The 3 tables work to make a individual part schedule. I can take the schedule and look to see what speciffic part goes to that model in sequence. Some people have multiple parts such as a flywheel and flange or a bracket. I would like to be able to pass out this to each person to manage their parts.

I've joined the 3 but now I would like multiple columns for the parts for the different categories, I have no problem geting it to work just one category but when I try to add a 2nd column in for the category I get a slew of multiples and duplicates.

Can I stuff that join between category and part into one field so it will put all the flywheels into one column and all the brackets into another on the same schedule? If so, how do I do it.

I am not all that fluent in SQL yet but I am learning.

Here is the SQL for the part selection. Here is where I would like to add the 2nd or 3rd column. To pull multiple part_ids

SELECT dbo_V_MNB_Model_Part.line_id, dbo_V_PROD_Schedule.sequence_number, dbo_V_PROD_Schedule.model_number, dbo_V_PROD_Schedule.balance_to_start, dbo_V_PROD_Schedule.balance_to_crate, dbo_V_PROD_Schedule.quantity, dbo_V_MNB_Category_Part_All.part_id
FROM (dbo_V_MNB_Model_Part INNER JOIN (dbo_V_MNB_Category_All INNER JOIN dbo_V_MNB_Category_Part_All ON (dbo_V_MNB_Category_All.category_id = dbo_V_MNB_Category_Part_All.category_id) AND (dbo_V_MNB_Category_All.category_id = dbo_V_MNB_Category_Part_All.category_id)) ON dbo_V_MNB_Model_Part.part_id = dbo_V_MNB_Category_Part_All.part_id) INNER JOIN dbo_V_PROD_Schedule ON (dbo_V_MNB_Model_Part.line_id = dbo_V_PROD_Schedule.line_id) AND (dbo_V_MNB_Model_Part.model_number = dbo_V_PROD_Schedule.model_number)
WHERE (((dbo_V_MNB_Model_Part.line_id)<"99") AND ((dbo_V_MNB_Category_All.category_id)="M20"))
ORDER BY dbo_V_MNB_Model_Part.line_id, dbo_V_PROD_Schedule.sequence_number;

And from that I join it with the schedule with this SQL that provides the correct list with 1 part number.

SELECT DISTINCT qry_sched.line_id, qry_sched.sequence_number, qry_sched.model_number, qry_parts.part_id, qry_sched.balance_to_start, qry_sched.balance_to_crate, qry_sched.quantity
FROM qry_sched LEFT JOIN qry_parts ON qry_sched.model_number = qry_parts.model_number;

Please let me know if you need more information or explination. Some lines will have no part number, say if that model dosen't have a bracket I need that line in the schedule anyway with a blank. I can't modify the database, only query it.

EDIT: Also, I was hoping if this was possible to put into 1 big SQL statement. I am using MS access as a workaround to the MSQuery interface that is not as...nice. I would like to work out the result and then paste the sql into MSQuery so we can dump the results into excel.

[Bmo]

I have figured it out. My base queries were extremely over complicated and were adding incorrect values.

I went through everything and then made a part1 and part2 query and joined that with the schedule.