Query Not Listing All Items

[Rawb]

I work at a glass manufacturing plant. We cut, temper, etc. glass for customers. All our information is set up in an Access 2000 database.



We've just started being able to track the amount of scrap we throw away at each station (for calculating efficiency) and I've run into an interesting problem. . .

The different types of glass we have available each has a unique "Glass Number" assigned to it. What this means is that, untempered (annealed) glass will have a different number once it gets tempered (even if everything else about the glass is exactly the same: Annealed 1/4" clear glass has a different number than Tempered 1/4" clear glass.). To keep track of this, we have a cross reference table that shows which glass numbers are all linked to the Annealed type.

I'm trying to generate a report that shows all the glass we've thrown away for a given date range. The problem is that I want to "convert" all the glass numbers to their parent/annealed number. How can I get all this glass to show up (with the annealed number)?

There are 3 tables being referenced. . .

GlassLink:
This is our cross reference table. It lists every non-annealed glass number we can make and links it back to the annealed number.

ParentGlass / ChildGlass
001         / 003
002         / 004

PartMaster:
This is our master list of parts. Among other things, it includes a list of the glass part numbers and descriptions.

PartNo / Desc
001    / 1/8" Clear - Annealed
002    / 1/4" Clear - Annealed
003    / 1/8" Clear - Tempered
004    / 1/4" Clear - Tempered

ScrapEntry:
This is our list of glass that's been thrown away. The Form individuals use to enter glass into this list is designed to allow them to enter ANY glass number in our system. This means that the person entering the data doesn't have to stop and think "hrm, well this says it's glass number 003, but that's really 001."

GlassNo / Date  / Qty / Height / Width  / Operation
002     / 05/10 / 3   / 12.000 / 13.000 / Cutting
003     / 05/10 / 1   / 12.000 / 13.000 / Drilling
004     / 05/11 / 1   / 15.000 / 16.000 / Edging
003     / 05/11 / 1   / 11.000 / 13.000 / Tempering
003     / 05/12 / 2   / 10.000 /  8.000 / Tempering
001     / 05/12 / 1   / 12.500 / 13.250 / Cutting

How can I get the query to show everything as it's GlassLink.ParentGlass glass number? I've tried linking GlassLink.ParentGlass to ScrapEntry.GlassNo, but that seems to only list items that actually link to a parent glass number (and not any items that ARE the parent glass number, since those aren't linked to themselves).

[TheShabz]

From what I understand, you have that xref table to link the two types of similar glass to each other. You also want to just calculate how much of that glass, regardless of status (annealed, tempered) was thrown away. So basically you want to add the amounts of partNo 001 and 003 and display it as the quantity of 1/8" Clear glass was thrown away.

If my understanding is correct, here's what I would do.
1. get rid of the xref table unless it will be useful for something else.
2. make third column in PartMaster, called "status" for our purposes.
3. remove the "annealed" or "tempered" part from your Desc field
4. add "annealed" or "tempered" in your status field.
5. resulting table would be
001, 1/8in clear, annealed
003, 1/8in clear, tempered
note: i replaced the " indicator for inch with in because having the " and using it in your queries can complicate indicating a string.
6. now you can run a query like:
SELECT PartMaster.Desc, sum(ScrapEntry.Qty),
FROM ScrapEntry, PartMaster
WHERE ScrapEntry.GlassNo = PartMaster.PartNo AND PartMaster.Desc = "1/8in Clear"
GROUP BY PartMaster.Desc

syntax may be off on the sql (doubt it). It's the concept im trying to get across. Let me know if I misunderstood your needs. If that solves your issue, please mark the thread solved.

[Rawb]

TheShabz,

You understand what I want to do correctly, unfortunately changing the format of the underlying tables isn't an option. We're using a Split DB where each facility maintains their own backend database, but the frontend application is something that's "officially" maintained by our corporate offices.

Because of that, I'm limited to making "basic" changes that can be rolled out again each time the corporate offices deploys an updated frontend application. I'm also forced to keep our backend table formats identical to all the other plants as the corporate offices will periodically request a copy of our data (and expect it to work with their frontend).

I am, however, looking into making a temporary table that does exactly what you say. Then I can query from the temp table.

Once I get everything working, I'll go ahead and "solve" the thread, but I'd like to keep it open until then for any additional comments

Thanks!

[TheShabz]

keep things the way they are. (still try to get rid of " as the inch indicator)
change the SQL from:

SELECT PartMaster.Desc, sum(ScrapEntry.Qty), 
FROM ScrapEntry, PartMaster
WHERE ScrapEntry.GlassNo = PartMaster.PartNo AND PartMaster.Desc =  "1/8in Clear"
GROUP BY PartMaster.Desc

TO

SELECT PartMaster.Desc, sum(ScrapEntry.Qty), 
 FROM ScrapEntry, PartMaster
 WHERE ScrapEntry.GlassNo = PartMaster.PartNo AND PartMaster.Desc Like  "1/8in Clear*"
 GROUP BY PartMaster.Desc

using the * wildcard will give you anything that starts with "1/8in Clear" so both annealed and tempered will show up.

[Rawb]

How would I be able to do that for multiple glass types? The GlassLink Table is about 100 Records and we have many different thicknesses and colors/shadings of our 30(ish) annealed types.

I'd hate to have to explicitly state every annealed type's description in the Query. . .

[TheShabz]

More ideas =]

You can do it from a form and the xref table. have a combobox that contains the ID and Desc of parentGlass . when a type is chosen, have the button run a query like the one I have above but the WHERE would be a complicated one. SQL is probably off here. I'm not the best with SQL off the top of my head but here goes. The parens part of the WHERE is pseudo-SQL. hopefully someone else who knows more than me can fix it.

SELECT PartMaster.Desc, sum(ScrapEntry.Qty),
FROM ScrapEntry, PartMaster, GlassLink
WHERE ScrapEntry.GlassNo = PartMaster.PartNo AND PartMaster.PartNo = GlassLink.ParentGlass AND (PartMaster.PartNo = (ID field from form) OR PartMaster.PartNo = (subquery that selects the childGlassNo from the xref table)
GROUP BY PartMaster.Desc

[Rawb]

Eeeeeenteresting. . .

That looks doable. I'd have to figure out the subquery (which I'm horrible at). I'll have to look into that more tomorrow!

[Rawb]

Well,

Unfortunately I wasn't smart enough to do it with just the Query so I went the temp Table route. I designed the table two have two PartNo fields for each Record, one for the "actual" PartNo and one for the "annealed" PartNo.

Every time the report is viewed I run two queries. The first one deletes all the records in the table and the second one re-populates it with the "raw" data from the ScrapEntry Table (It leaves the "annealed" PartNo blank and just enters the "actual" PartNo). Then I run through each Record in the Table and, for each one that has an entry in the GlassLink Table, I record that as the "annealed" PartNo. For those that don't have an entry, I assume the number IS the annealed number and just use that.

Then I build my Report based on the temp Table instead of on a Query.

It took me quite a while to get all the error checking in. Now that it's done though, it works like a charm!