Help with some logic

[ryetee]

I have a query effectively using 2 tables, Model and Model Parts.

Model contains the model number and other attributes such as serial number.
Model parts contain all the parts used to repair the model.
There are many models with the same number; the serial number makes it unique. For each model there can be many parts.

For each model I want to take a count of each different model part used.
This is quite easy using group by and count. However I want to go one stage further. At he moment I can produce something like this

Model Part Count
11111a 11234 22
11111a 14321 9
11111a 15523 3
11111a 13232 8
11111b 11234 5
11111b 14321 12
11111b 95523 2
11111b 93232 2

What I want to be able to differentiate is for a specific part whether or not another specific part was used on the repair. For example let's say I'm interested in part 14321 and 11234. For part 14321 I want 2 counts depending on whether it was used on the same repair as 11234. So I get something like (and this is made up)
Model Part Count
11111a 11234 22
11111a 14321 2 (used twice on the same repair as 11234)
11111a 14321 7 (7 times not on same repair)
11111a 15523 3
11111a 13232 8
11111b 11234 5
11111b 14321 1 (used once on the same repair as 11234)
11111b 14321 11 (7 times not on same repair)
11111b 95523 2
11111b 93232 2

Hope this is clear!!

 

[plog]

Your desired output seems ambigous, in no way can you look at 1 record and determine what data you are looking at. This data alone:

Model, Part, Count
122333a, 999, 14

Doesn't let you know what that record is for. Is 14 the total of all repairs, or just those in conjunction with part 11234? You don't know. You would have to look for another record, then do math in your head, then you would be able to determine that.

Wouldn't an output like so be better:

Model, Part, TotalRepairs, 11234Repairs
122333a, 999, 14, 5


Or Even

Model, Part, TotalRepairs, RepairType
122333a, 999, 14, All
122333a, 999, 5, 11234

All 3 options are possible, just don't want to help you achieving something that isn't what you truly want.

 

[ryetee]

Your desired output seems ambigous, in no way can you look at 1 record and determine what data you are looking at. This data alone:

Model, Part, Count
122333a, 999, 14

Doesn't let you know what that record is for. Is 14 the total of all repairs, or just those in conjunction with part 11234? You don't know. You would have to look for another record, then do math in your head, then you would be able to determine that.

Wouldn't an output like so be better:

Model, Part, TotalRepairs, 11234Repairs
122333a, 999, 14, 5


Or Even

Model, Part, TotalRepairs, RepairType
122333a, 999, 14, All
122333a, 999, 5, 11234

All 3 options are possible, just don't want to help you achieving something that isn't what you truly want.

Plog it's my bad way of explaining things
Each model is only unique with the serial number. For each model/number serial number a number of parts are replaced. These are the model parts.
I may have a number of the same model that have different parts replaced.

I can easily do a "total" query that tells me model x had part y replaced z times. For 1 specific part I want to output 2 counts. One that says this is the number of times this was replaced when part abc was replaced as wellwith the other being when part abc wasn't replaced.

Is that clearer?

 

[plog]

Nope. There are 3 different formats for output listed on this page which one do you want?

A - your initial post's example
B - first example in mine
C - second example in mine

Which output do you want?

 

[ryetee]

Nope. There are 3 different formats for output listed on this page which one do you want?

A - your initial post's example
B - first example in mine
C - second example in mine

Which output do you want?

Your 2 examples are missing the point. I'll put it a different way. I have 5 models to repair. For simplicity they are all the same model number 11111 but in reality will be a lot more.
1. On the first model I replace the following parts
part a
part b
part c
2. On the second model I replace the following parts
part c
part d
part e
3. On the third model I replace the following parts
part a
part c
part d
4. On the fourth model I replace the following parts
part b
part e
part f
5. On the fifth model I replace the following parts
part d
part e
part b

Doing the easy part first I can easily produce

model part count
11111 part a 2
11111 part b 3
11111 part c 3
11111 part d 3
11111 part e 3
11111 part f 1

Ie I’ve broken down what parts were used for model 11111. Now the bit I’m struggling with is I want to know how many models were repaired that both part a and (say) c were replaced.
So I was after
11111 part a 2
11111 part b 3
11111 part c 2 (with part a)
11111 part c 1 (not with part a)
11111 part d 3
11111 part e 3
11111 part f 1

That clearer?

 

[plog]

I seriously don't think you are reading my posts. I have read and understood both of yours.

I have no idea why you continue to think I don't understand your issue. Reread both my posts. At no point do I ask for clarification for what you want. In both I questioned the layout of the expected data. And that issue remains.

Look at the expected data at the end of your last post. See how you had to use a parenthetical note to help clarify which record was for what data? My posts were not about how to get there, my post was about making that parenthetical note part of the resultant data so you could determine which record was which.

Again, reread my posts. I don't have any issue about the logical issue of your data. My concern is with the layout of what you want.

About to give up.

 

[ryetee]

I seriously don't think you are reading my posts. I have read and understood both of yours.

I have no idea why you continue to think I don't understand your issue. Reread both my posts. At no point do I ask for clarification for what you want. In both I questioned the layout of the expected data. And that issue remains.

Look at the expected data at the end of your last post. See how you had to use a parenthetical note to help clarify which record was for what data? My posts were not about how to get there, my post was about making that parenthetical note part of the resultant data so you could determine which record was which.

Again, reread my posts. I don't have any issue about the logical issue of your data. My concern is with the layout of what you want.

About to give up.

It's been a long long day and I have read your posts!
Your examples aren'tquite what I'm after
Model, Part, TotalRepairs, 11234Repairs
122333a, 999, 14, 5
and
Model, Part, TotalRepairs, RepairType
122333a, 999, 14, All
122333a, 999, 5, 11234

For a start 11234 is a part number but you have 999 as the part number so your example (to me) says on model 122333a part 999 was repaired 14 times of which 5 were 11234. the latter can not be the case as the part is 999.
What I want is
Model, Part, TotalRepairs, Numberwith partabcd
122333a, 11234, 14, 5
122333a, 98765, 14, 0 (because not interested if it was repaired with partabcd).

How's that?

 

[ryetee]

Fixed this (plog if you're interested).

Created a calculated field that for the part I was interested ran a DCOUNT on the repair for the second part I was interested in. If it was present I output "present" otherwise "".
I can then use this calculated field in a group by when counting each part.