A real doozy of a problem.

[factotum]

A candy bar to anyone that can help me with this one, because I have no idea how to accomplish this. The code so far:

SELECT Count(L.ProjectID) AS [Count], L.ProjectID, L.ProductID, L.ProductDescription, L.MNEMONIC, C.ProjectKey, C.Product
FROM [L. Data] AS L LEFT JOIN [C.] AS C ON L.ProjectID = C.ProjectID
WHERE (((C.Project_Type)<>&quot;Site Rollout&quot;) AND ((C.Version)=&quot;millennium&quot;))
GROUP BY L.ProjectID, L.ProductID, L.ProductDescription, L.MNEMONIC, C.ProjectKey, C.Product
ORDER BY L.ProductID, L.MNEMONIC, C.ProjectKey;

This will give me output (minus the count and stuff I've already accomplished) looking like:

+-----------+-----------+-------------+------------+---------+
| ProjectID | ProductID | ProductDesc | ProjectKey | Product |
+-----------+-----------+-------------+------------+---------+
|     1001  |    AA     |   Aaaaaaaa  |    101     |   Aaaa  |
+-----------+-----------+-------------+------------+---------+
|     1002  |    BB     |   Bbbbbbbb  |    102     |   Bbbb  |
+-----------+-----------+-------------+------------+---------+
|     1002  |    CC     |   Cccccccc  |    102     |   Bbbbb |
+-----------+-----------+-------------+------------+---------+
|     1003  |    DD     |   Dddddddd  |    103     |   Ccccc |
+-----------+-----------+-------------+------------+---------+
|     1003  |    EE     |   Eeeeeeee  |    104     |   Ddddd |
+-----------+-----------+-------------+------------+---------+

What I want to limit this query further to show the 4th and 5th lines only. Reason: I want to identify only those rows that have one ProjectID for multiple ProjectKeys. I have no idea how.

Any help would be greatly appreciated. Thanks.

 

[MichaelRed]

A layered query would work. First query is:

SELECT DISTINCT tblProdProj.ProjectID
FROM tblProdProj INNER JOIN tblProdProj AS tblProdProj_1 ON tblProdProj.ProjectID = tblProdProj_1.ProjectID
WHERE (((tblProdProj_1.ProjectKey)<>[tblProdProj].[ProjectKey]));


This should result in ONLY the single project key (1003), which is used as the left outter join back to your current query. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[factotum]

Hmmm, going through this, and the problem I run into is that you create the virtual table tblProdProj_1, and then try to map where ProjectKey isn't equal between the realized and the virtual, but ProjectKey and ProductID exist in two different tables. Could your solution be tailored (by adding the table to the FROM clause... can you do that?) or will it not work.

Thanks,

=Daniel

 

[MichaelRed]

of vcourse it 'works'. I had (have) no intention / desire to recreate the entire process, just to illustrate the process. You need to 'adjust' the table names and any other aspects of the query to match your db!tbl.fields

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[factotum]

You mean I just can't cut and paste? No wonder it didn't work.

Seriously, after reading some about layered queries (16 hits on Google-- one that sort of applied), I'm not understanding how you're tying the two layred queries together. Your code, if I apply it to the table that makes up the second two columns ([C.] in my example), gives me the single Product you speak of (sorry, I've been at this too long and tried squeezing your code onto the first table, which confused me). You say:
This should result in ONLY the single project key (1003), which is used as the left outter join back to your current query. Maybe my brain is fried, but I can't figure out how to tie the two back together and still have all the columns intact. I appreciate your help.

 

[MichaelRed]

Hopefully, the long winter nap has given the tired cells the propper opportunity to regernerate whatever tired cell need to do their thing. As to the issue (not?) at hand, consider that you already have the recordset which includes the two cols of interest, so even a copy of that would certainly suffice as the source for the snippet of SLQish I sent the last time, so the issue of the source there is simply how energetic you want to be in reoving the unnecessary fields - or wheather it is simply 'better' to only need to maintain the single query object, In either instaance, the query I suplied should provide the simple list of projectids matching the criteria. Using this a join to your statement you should be able to limit the porjectids to those with multiple associated project keys. Frankly, I'm confused as to what is so confusing?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[factotum]

First off, let me point out that I have now been using SQL for hard-core data manipulation for about two weeks (I was familiar with the technology for web-based stuff, but those are usually easy problems). Cut me some slack on my stupidity, alright? I appreciate your help, but I feel like the cost is humiliation. I posted this problem because I don't know how to do it. What is a layered query? Can I not do this in one query? By layered query, do you mean two? I've used, and heard of, a subquery, but I'm not familiar with the layered variety. The columns are necessary. These will be used to generate a report that go to executives who will make decisions on them. I was hoping I could somehow add your code to the query I've already written. The database we're working on already has almost a hundred queries, so I'm trying to do this in one object. I'm going to go and hit this some more, and see what I can do. Once again, Michael, I appreciate your help. I'm sorry I'm not getting this right off.

 

[GoDawgs]

Hey Daniel. First off let me begin by saying I don't think I can totally solve this problem on my own... I think he's got it right (I'm sure I'll get yelled at for saying &quot;think&quot. The first query he gave you should give you a list of all of the project id's that have different project keys. You can then use that as criteria for your original query I believe...so basically put that in your WHERE statement using IN:

WHERE ProjectID IN (SELECT....his query)

I think this will work, but I'm not positive...hopefully it will at least get you in the right direction. Hope that helps.

Kevin

 

[factotum]

Kevin. Thank you so much. For starters, my biggest problem was stemming from not using the

IN

keyword. I was using the

=

operator, and kept getting the error in Access saying that at most one record could be returned by the subquery (My understanding would probably increase if I knew why, but I don't). Here's what I've ended up with:

SELECT L.ProjectID, L.ProjectID, L.ProductDescription, L.MNEMONIC, C.ProjectKey, C.Product
FROM [LData] AS L LEFT JOIN [CData] AS C ON L.ProjectID=C.ProjectID
WHERE ((C.ProjectID) IN (SELECT DISTINCT C.ProjectID FROM [CData] AS C INNER JOIN [CData] AS C1 ON C.ProjectID=C1.ProjectID
                      WHERE(C1.ProjectKey<>C.ProjectKey))
      )

AND (((C.Project_Type)<>&quot;Site Rollout&quot;) AND ((C.Version)=&quot;Millennium&quot;))
GROUP BY L.ProjectID, L.ProjectID, L.ProductDescription, L.MNEMONIC, C.ProjectKey, C.Product
ORDER BY L.ProjectID, L.ProjectID, C.ProjectKey;

Unfortunately, this query takes forever to run, so I'm not able to check the results. It's still running and I'll let you know if it's successful. I ran Michael's query by itself and checked the results against what I knew to be the right outcome, and it works. Thanks Kevin for taking the time to help me out. I really appreciate it.

=Daniel

 

[GoDawgs]

No problem Daniel, glad I could help.

 

[factotum]

Well, it doesn't work. I'm not sure why. I just can't get the two to work together. I must be doing something very stupid someplace.

 

[MichaelRed]

I don't mean to cross, crabby, condesending or to humililate you. On the other hand, you fail to give some of the ingredients for the soup - then ask for the original recipe plus the addition of some spice.

The sample set of data displayed is certainly 'minimalist', and provides little insight into the intent or data types of the actual problem. The sql atatement refers to fields in the source table(s) which are not shown in the sample data, so any attempt to 're-create' the process is doomed to the failure of being incomplete - and that is the best result.

I sympathize with the headache, but I do not volunteer to share it with you. Again, I'm somewhat confussed as to the cause of your confusion. I believe my previous post includes sufficient info for you to complete the task - while all of your posts do not provide the information to usefully and completely 'hand you' the completed answer.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over