Bad Design?

[teiben]

I have 5 plants (when this was orginally created there was 1) I need to develop a report showing defect by suppliers. A user would select a supplier from a combo box, and using a query would show each reject number by supplier. I can get query to work, for only 1 plant as soon as I select another plant no records display. Any ideas

 

[KeithG]

Can you post the SQL also?

 

[neileg]

You're probably using inner joins in your query. However your structure is not right. You should only have one table for rejects with a field to show which plant is involved.

 

[jdraw]

I have 5 plants (when this was orginally created there was 1) I need to develop a report showing defect by suppliers. A user would select a supplier from a combo box, and using a query would show each reject number by supplier. I can get query to work, for only 1 plant as soon as I select another plant no records display. Any ideas

I think you are dealing with ( at least)

Suppliers
Plants
Rejects

Is Defect same as a Reject or a type of Reject?

You may also have Reject_Reasons , or something to show something did not meet a certain standard.

First step in any of these database issues is to write down in English (or some language) what the Things are you are dealing with, then some of the questions (descriptions of Reports) you may be trying to solve.

For example:

We have X Plants that manufacture Widgets. We get raw materials from Z Suppliers. Sometimes the manufacturing is faulty and the Widget fails a quality test. A widget may fail for N reasons.

We would like to know what raw materials came from which Supplier.
We would like to know how many Widgets are Rejected at which Plant.

Anyway, something along these lines can clarify things for you when you begin to design your application.

Good luck.

 

[teiben]

Without designing the entire database, is there a way to get a report with the supplierid from each table into one query/report?
I guess that's what happens if its not designed correctly from the beginning
jdraw - what you wrote is that your normalizaion technique? I have another project and I was curious how others normalize...

 

[teiben]

this is the sql, for one plant table, which works, but I can't add anymore tables in
SELECT Supplier.[Supplier ID], Supplier.[Company Name], tblChesterfield.[Reject Number]
FROM tblChesterfield INNER JOIN Supplier ON tblChesterfield.[Supplier ID] = Supplier.[Supplier ID]
WHERE (((Supplier.[Supplier ID])=184 Or (Supplier.[Supplier ID])=172 Or (Supplier.[Supplier ID])=334 Or (Supplier.[Supplier ID])=400 Or (Supplier.[Supplier ID])=290))
ORDER BY Supplier.[Company Name];

 

[jdraw]

Without designing the entire database, is there a way to get a report with the supplierid from each table into one query/report?
I guess that's what happens if its not designed correctly from the beginning
jdraw - what you wrote is that your normalizaion technique? I have another project and I was curious how others normalize...

What I wrote was just some thoughts on what the database was made up of, and what it should be able to provide basic answers to.

Here's a great link on normalization. There are others, but this is a good reference that is fairly easy to read.

http://r937.com/relational.html

 

[boblarson]

Just a quick comment here. It is going to be painful to normalize your database, but it will become even more painful the further you band-aid it along. You really should suck it up and do it now as it will save you, and plenty of others, headaches that will come if you don't. I know, not a good prospect, but one we can liken to the old Fram Oil Filter commercials where the guy says, "You can pay me now, or pay me later" which infers that you can pay a little now for a good filter or pay for an entire engine job later. I think you are a little beyond the filter stage, but you may not be in to the entire engine later stage.

 

[jdraw]

this is the sql, for one plant table, which works, but I can't add anymore tables in
SELECT Supplier.[Supplier ID], Supplier.[Company Name], tblChesterfield.[Reject Number]
FROM tblChesterfield INNER JOIN Supplier ON tblChesterfield.[Supplier ID] = Supplier.[Supplier ID]
WHERE (((Supplier.[Supplier ID])=184 Or (Supplier.[Supplier ID])=172 Or (Supplier.[Supplier ID])=334 Or (Supplier.[Supplier ID])=400 Or (Supplier.[Supplier ID])=290))
ORDER BY Supplier.[Company Name];

Here is another SQL that does what you have. Neither one is right -- there just asking the same question a slightly different way.

SELECT Supplier.[Supplier ID]
, Supplier.[Company Name]
, tblChesterfield.[Reject Number]
FROM
tblChesterfield 
,Supplier 
WHERE tblChesterfield.[Supplier ID] = Supplier.[Supplier ID]
AND
Supplier.[Supplier ID] IN (184 ,172,334 ,400 ,290)
ORDER BY 
Supplier.[Company Name];

How much data do you have in your tables?

 

[teiben]

About 1000 records between the 5 tables, or going back to 1998. I suspect boblarson is correct about the redesign.....but first I wanted to exhaust the band aid approach

 

[jdraw]

About 1000 records between the 5 tables, or going back to 1998. I suspect boblarson is correct about the redesign.....but first I wanted to exhaust the band aid approach

I agree with Bob and the Fram Oil Filter.

If there is any redesign of the application for any purpose, you should consider normalization and the database structure.
It will save you time and money in the longer term. If the data goes back to 1998 , does the application go back that far aw well?