Querying an exact set of values?

[neato]

Hi all,

I've found myself in a bit of a nightmare situation, in that I did Computing at college (a good while back!) and my manager has a database they would like me to query.
However, its very old and the DBA retired 5 years ago. I've opened it up and it seems like a mess to me, but I'm sure there was method in his madness at one point.

It holds tables for the machines we have and the parts each machine requires. We also have a database of spare machines and the parts that are in them.

Given the nature of the machines, some parts aren't worth trying to pull out singularly, only as part of a set.

I've been asked to put together something queries which of our old parts/machines can be reused.

So I have a table of current machines and their parts and when they were last replaced.

I have a query that says 'Show me all the machines that have parts over 5 years old.' I get a list of machines and their parts

Can I then take that whole list of parts and search the table of spare machines any parts and find that exact list of parts?

I don't mind if the machine has more parts, but it must match that exact set of parts and that set of parts should be contained in one machine.

I've added an example that is a cut down version of what I have:

Basically say we might want to replace the parts in Current machine 10002.

I query CurrentMachines for which parts it has.
I then want to use the results of that to query the spares. But I only want to return spares that has all three parts that machine 10002 has.

All I can make it do at the minute is find the spares with any one of the parts that match the 10002 list.

 

[Geotch]

It can probably be done with less queries, but this works.

These queries first match the number of parts in each machine, both must be equal. Then it makes sure each of the parts match.

 

[neato]

Thanks for your help, that's given me a great starter for ten, I was trying to fit it into one or two queries, maybe I'm not breaking it down enough.

Sorry, just for my own understanding here, you've done

1. Select all the fields from CurrentMachines
2. Counted the number of parts each of our current machines have
3. Counted the number of parts each of our spare machines have
4. Take the results of the previous two queries and join them together and see if the current and spares have the same number of parts
5. For the spare machines that do have equal number of parts, check if their parts match the current machine?? This is where I'm loosing it slightly, I'm not sure I follow what each of the Part Match queries do.

Also would this mean that the spare machine would have to be an exact part match of a spare? Or could the current machine parts be a subset of all the parts in a spare machine. I think, ideally, I need it to find subsets (the older machines used to do multiple jobs, we've since switched to ones that focus on one specific job)

 

[Geotch]

Yes it's only finding exact matches. As i was doing it, wondered if you'd pull from a machine with extra parts. I can try to make changes to it tomorrow if you dot figure it out.

 

[neato]

Thanks for that. I'll give it a bash and see if I can work on what you've given me to help out, in all honesty, you've already managed to flip my approach on it's head. Which is a great thing as I'd spent the last few hours trying to do something that clearly wasn't working!

 

[Mihail]

Geo was faster than me.
Anyway I upload my DB. Seems to have the exact same logic as Geo's.

 

[neato]

This is great - thanks!
It probably sounds really daft but the visual element really helped me get my head round what the queries are actually doing.

I'm trying to use this alongside Geo's more split out queries to give me a basis for finding parts when they're part of a larger part set from an older multi-function machine.

I'm then hoping to feed it into a report that will hopefully show something like for all our current machines, these spare machines have the part combination that could be refurbished and use as replacements.

I'll post back when I've got something of use! Or more likely when I've fallen at the next hurdle Thanks so much for your help. Reading a few refresher guides on SQL has helped me think more logically and on a step by step basis liek you guys, so hopefully I might get a bit further this time

 

[neato]

Hi, me again I've been rebuilding queries similar to Mihail's but it already seems to work on machines with subsets of replaceable parts, but as far as I can tell Mihail's queries work like Geo's in terms of comparing parts, am I missing something here?

Is this working by coincidence?

 

[Mihail]

Before posting my DB I have read what you wrote:

Sorry, just for my own understanding here, you've done

1. Select all the fields from CurrentMachines
2. Counted the number of parts each of our current machines have
3. Counted the number of parts each of our spare machines have
4. Take the results of the previous two queries and join them together and see if the current and spares have the same number of parts
5. For the spare machines that do have equal number of parts, check if their parts match the current machine?? This is where I'm loosing it slightly, I'm not sure I follow what each of the Part Match queries do.

It is why I said:

Geo was faster than me.
Anyway I upload my DB. Seems to have the exact same logic as Geo's.

Is this working by coincidence?

Yes. Or... no.
YES because we work in the same time, in different sides of the world without to know even that the "other" try an answer for you.
NO because after some experience we (all of us) started to think the same.

More: Like Geo

It can probably be done with less queries, but this works.

I "feel" that can be done more accurately.
And I'm sure that the "SQL guys" can do this using a single SQL string.

From your last post I can't understand if you still have troubles or this post was a simple comment

 

[neato]

I'm still having trouble but only because I want to maybe expand on this to try and generate a report that shows every machine we have, which spares match.

Rather than just taking the queries you guys have kindly provided. I'm trying to get my head around them so I understand how they work and hopefully if we need any modifications in the future, I won't have to lean on you guys too much.

Geo's uses a count of current parts and spare parts and only shows a result if a current machine has the same amount of parts as a spare.

Yours seems to pick up collections of parts even if the spare has more parts. But I'm struggling to work out how this works from your SQL. (Or how it differs from Geo's)

This is very much my unfamiliarity around Access and SQL though, I'm doing some background reading on the terms you've used in your queries. Hopefully I'll get my head round it

 

[Mihail]

I'm still having trouble but only because I want to maybe expand on this to try and generate a report that shows every machine we have, which spares match.

Fill my DB with more data (I think that real data is not out of your policy) and upload it.
I'll try to help you.

You should know that your DB is NOT normalized.
So, anything you try is very hard (or impossible).

 

[Geotch]

You should be able to follow my queries if you have them in name order. Start at the first one and understand what it's doing, then move to the next.

I added one more spare machine to the data, it has 5 parts with three matching a machine that needs the parts.

I changed qryZ_WhichMachineHaveEqualParts to look for spares with the same or more parts than the machine is looking for. Example, machine needs parts 1,2,3 if a spare has 3 or more of the same parts it is now included in future queries to check the actual parts.

I added query qryPartsInSpareMachines which adds up the total parts of the spares and I use it at the end to show if a match has extra parts.

I added a report for an example. It shows all matches even if the spare has extra parts.

Let me know if you need anything else.

 

[Geotch]

Fill my DB with more data (I think that real data is not out of your policy) and upload it.
I'll try to help you.

You should know that your DB is NOT normalized.
So, anything you try is very hard (or impossible).

No hard feelings, but I disagree with the "hard (or impossible)" comment.

 

[Mihail]

Try this (see attachment)

And... I maintain my comment