I need Query Help

[cadsvc]

I am in the Quality Dept and have a DB for our product specifications. Key field is PRODUCT ID. To get the PARAMETERS (length, width, ect) in correct order I use a field called ORDER. (Each parameter has min, target, max, and notes fields)



I want the Maintenance Dept's and the Engineering Dept's to edit their own tables that have fields PRODUCT ID, PARAMETER, and ORDER.

I want my query and reports to list the Quality Dept parameter's for the PRODUCT ID first in correct order, the Maintenance Dept parameter's for the PRODUCT ID second in correct ORDER (FIELD), and the Engineering Dept parameter's for the PRODUCT ID third in correct ORDER (FIELD)

a. If I add department ID codes will my query allow me to get 3 Dept's data on the report? The query I will create will have a lot of fields for QA (min, target, max, notes), Maintenance (min, target, max, notes), and Engineering (min, target, max, notes).

How do I do this? - It won't work. Out put of the query will have each line blank

Is this too complicated?

[TheShabz]

I'm trying to visualize what's going on but am failing. Can you provide a real-life example of what is happening?

[cadsvc]

Thanks for responding.

QA Dept Maintained Table 1 Fields
Product ID = 1
Width Parameter has a Min Field, Target Field, Max Field
Thichness Parameter has a Min Field, Target Field, Max Field
Plus Others

I have made a report for the QA Dept that works. Now to add info from the other Depts

Engineering Dept Maintained Table 1 Fields
Product ID = 1
Temperature Parameter has a Min Field, Target Field, Max Field
Pressure Parameter has a Min Field, Target Field, Max Field
Plus Others

Maintenance Dept Maintained Table 1 Fields
Product ID = 1
Dye Parameter has a Min Field, Target Field, Max Field
Screw Parameter has a Min Field, Target Field, Max Field
Plus others

I need a query that will can make a report that shows the following:
QA Dept Maintained Table 1 Fields
Product ID = 1
Width Parameter has a Min Field, Target Field, Max Field
Thichness Parameter has a Min Field, Target Field, Max Field
Temperature Parameter has a Min Field, Target Field, Max Field
Pressure Parameter has a Min Field, Target Field, Max Field
Dye Parameter has a Min Field, Target Field, Max Field
Screw Parameter has a Min Field, Target Field, Max Field

Thanks for looking at this.

[TheShabz]

So you just want to combine them together but have the order of fields (left to right) in your query be the way you want it? Just select them the way you want it in the query builder and make sure you are joined by the Product ID for all tables. That way you wont have anything missing.

[cadsvc]

So you just want to combine them together but have the order of fields (left to right) in your query be the way you want it? Just select them the way you want it in the query builder and make sure you are joined by the Product ID for all tables. That way you wont have anything missing.

The QA Table has an Order Number Field, Engineering Table has an Order Number Field, Maintenance Table has an Order Number Field. Won't the query to make my report have to have a combined field to "Jam" each depts Order Numbers into so the report will look something like this...

QA Oder Numbers
Followed by Engineering Order Numbers
Followed by Maintenance Order Numbers

[TheShabz]

Were going from Products to Orders and I'm not following. Do you have a sample output that you can screenshot?

I get a feeling this will come down to whether you have any way of identifying via Order Number which dept it came from. Also, why are Orders in the same table as Products?

[cadsvc]

PRODUCT ID is a KEY. Groups of products are assigned to the same PRODUCT ID {nothing wrong with this field or data}

ORDER ID is a field used to properly get the data to display properly on the report. The values in ORDER ID are static.

I could list all data in the QA Table but want the Maintenance Dept to update their table that contains their data for a PRODUCT ID. If they need to change a value I don't want to have to do it at 3 am. I don't want to have the Maintenance Dept to be able to change my QA specifications.

The same for the Engineering Dept.

I know I can give them their own tables but when it comes to making a query for the SPECIFICATION REPORT is where my mental problem happens.

I can add the new tables to my query design, make relationships, and select the added fields but...

PRODUCT ID: #1 has 22 lines of data in my existing query, ADD Maintenance Dept has 30 lines of data, ADD Engineering Dept has 23 lines of data. How do I get the lines of data in correct sequence if I simplely add the tables to my working query used to make my report?

[TheShabz]

D'oh Order <> Sale. Curse this language of ours.
Well, when you create a single table from all 3 via make table query, youll get something like:
A 1 1 1 - - - - - -
A - - - 2 2 2 - - -
A - - - - - - 3 3 3
Where - is null values. I take it what you want is:
A 1 1 1
A 2 2 2
A 3 3 3
However, that wont work because the fields are different. The only way around it that I can think of is to name the fields exactly the same and add a field for DeptID where you can organize the data by department. Then when you run the query, it will show up up like:
A 1 1 1 QA
A 2 2 2 Eng
A 3 3 3 Maint
and then in your report you can adjust your field names accordingly and group by the DeptID. That's really the only way I can think of making this work without completely overhauling the database design. If this doesnt help, hopefully someone else can chime in with a better idea.