Query to compare one list of records with matching records from other lists

[edgaro]

Hello, forum. I'm missing some query knowledge right now.

I have two tables like this:

tbProjects: ProjectID*, ProjectName
tbActivities: ActivityID*, ActivityName, Unit, UnitPrice, ProjectID (fk)

Now, let's say I have 4 projects, from 1 to 4, and each project has some ActivityNames that repeat, but change their UnitPrice. And this is what I need to analyze.

In a query, I know how to display all of the ActivityNames from Project 4, keeping them in the right order. But how do I display another two columns, where I can compare the matching ActivityNames from Project 1 and Project 3, BUT having the ActivityNames from Project 4 intact, no more, no less, all of them and in the right order? I don't really want to display ActivityNames from Project 1 and 3 if they do not match with the list provided by Project 4.

Does this make sense?


Thank you all in advance.

 

[sneuberg]

I'm not sure if this will help but in the query builder you can add a table more than once and they act independently. So you could add both tables twice and join them as you normally do but have one set do one thing and the other set do something else.

Hope this helps.

 

[arnelgp]

Select ZActivityNames, Z.UnitPrice, (Select UnitPrice From tblActivities As Table1 Where Table1.ActivityName = Z.ActivityName And Table1.ProjectID=01) As [Project 01-UnitPrice], (Select UnitPrice From tblActivities As Table2 Where Table2.ActivityName = Z.ActivityName And Table2.ProjectID=02) As [Project 02-UnitPrice], (Select UnitPrice From tblActivities As Table3 Where Table3.ActivityName = Z.ActivityName And Table3.ProjectID=03) As [Project 03-UnitPrice] From tblActivies As Z Where Z.ProjectID=04;"

 

[edgaro]

Select Z.ActivityName, Z.UnitPrice, (Select UnitPrice From tblActivities As Table1 Where Table1.ActivityName = Z.ActivityName And Table1.ProjectID=1) As [Project 1-UnitPrice], (Select UnitPrice From tblActivities As Table2 Where Table2.ActivityName = Z.ActivityName And Table2.ProjectID=2) As [Project 2-UnitPrice], (Select UnitPrice From tblActivities As Table3 Where Table3.ActivityName = Z.ActivityName And Table3.ProjectID=03) As [Project 3-UnitPrice] From tblActivities As Z Where Z.ProjectID=4;

Hi, arnelgp, I tried the above code and I think it's not working. It also warns me that it will only return one record. Care to elaborate how this works? I appreciate your help.

sneuberg: Thanks for your help, I'm gonna try this next.

 

[edgaro]

you could add both tables twice and join them as you normally do but have one set do one thing and the other set do something else.

I couldn't do it, it returns a huge amount of records when I do it that way. Could you explain a little more?

 

[sneuberg]

My suggestion was just a shot in the dark as I don't understand your requirement. Mr. arnelgp seems to get it so I guess I'm a little dense. You could help me understand what you want by providing some example data for the two tables and the output you are looking for.

Getting back to my suggestion if the two sets of tables aren't connected (joined) somehow I believe the query result is the permutation of both sets or something like that. That can be a lot of records.

 

[edgaro]

Ok, so let's say I have three grocery stores, named A, B and C.

In grocery store A, I have:
Oranges $6
Apples $9
Grapes $12
Celery $2
Radish $3

In grocery store B, I have:
Oranges $6
Apples $13
Celery $2
Radish $1
Cucumber $4
Onion $5

In grocery store C, I have:
Apples $9
Celery $2
Radish $3
Cucumber $4
Cabbage $2

These grocery stores have different prices and different products, but I want to compare the price of the products in grocery store B, compared to A and C, but I only want to see the products from store B. So, I'd want something like this:

Product - B - A - C
Oranges - $6 - $6 - N/a
Apples - $13 - $9 - $9
Celery - $2 - $2 - $2
Radish - $1 - $3 - $3
Cucumber - $4 - N/a - $4
Onion - $5 - N/a - N/a

Where the "N/a" is probably 0 or an empty space. It doesn't matter because if it has a price, then it exists in that grocery store.

 

[sneuberg]

I just got back in and will look at this in detail, but just for fun I suggest you try a cross tab query if you haven't already. Just use the wizard it won't take much of you time to get an output. Given what you are asking for I think you might find it useful.

Meanwhile I'll try and to get something for you that's more specific.

 

[edgaro]

The problem I have with these crosstab queries is that it would list all of the products from all of the groceries. Is there a way to limit it to only display the products that belong to grocery store B?

Thanks again

 

[sneuberg]

While I've not been able to come up with a specific solution to your problem, I can show you how to limit the output of a crosstab query to the activities of a specific project. Please look in the attached database. The output of query "qry Project B Activities" is limited to the activities of project B. This query is joined with the tables in qryProjectActivity which is used as the source for the crosstab query qryProjectActivity_Crosstab . The crosstab query was created with the wizard. Except for the column order this produces the output you wanted, I think.


Other than this I'm stumped. Maybe Mr. arnelgp will put his query in this database for you. I tried that but I couldn't figure it out.

 

[arnelgp]

SELECT Z.ACTIVITYNAME , (SELECT UNITPRICE FROM TBACTIVITIES AS T1 WHERE T1.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=1) AS [PROJ1-PRICE], (SELECT UNITPRICE FROM TBACTIVITIES AS T2 WHERE T2.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=2) AS [PROJ2-PRICE], (SELECT UNITPRICE FROM TBACTIVITIES AS T3 WHERE T3.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=3) AS [PROJ3-PRICE]
FROM (SELECT DISTINCT ActivityName
FROM TbActivities) AS Z;

 

[sneuberg]

Mr. arnelgp,

I plugged your query into the database I uploaded and it worked fine except that it doesn't restrict the activities to the project B (PROJ2) as Mr. edgaro wants.. Nonetheless it was relatively easy to modify it to do that by modifying the last select statement as shown in red below. Anyway this is really cool and I intend on learning all about subqueries.

SELECT Z.ACTIVITYNAME, (SELECT UNITPRICE FROM TBACTIVITIES AS T1 WHERE T1.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=1) AS [PROJ1-PRICE], (SELECT UNITPRICE FROM TBACTIVITIES AS T2 WHERE T2.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=2) AS [PROJ2-PRICE], (SELECT UNITPRICE FROM TBACTIVITIES AS T3 WHERE T3.ACTIVITYNAME=Z.ACTIVITYNAME AND PROJECTID=3) AS [PROJ3-PRICE]
FROM (SELECT DISTINCT tbActivities.ActivityName FROM tbProjects INNER JOIN tbActivities ON tbProjects.ProjectID = tbActivities.ProjectID WHERE tbProjects.ProjectName="B") AS Z;