Query Questions x 2

[Guest]

Please help me. My email address is (e-mail address removed)

I have 2 tables:

Table 1: Types
Company App Type
Co1 app1 1
Co1 app3 2
Co2 app3 3

Table 2: Apps
App
app1
app2
app3

Query1: I would like to create a query that looks like this but can't figure
out how to do it.

Query1:
Company App Type
Co1 app1 1
Co1 app2
Co1 app3 2
Co2 app1
Co2 app2
Co2 app3 3

Query2: I'd like a 2nd query to look like this:

Query2:
Company app1 app2 app3
Co1 1 2
Co2 3

Please advise me. Thanks. Happy New Year!!!
Peter

 

[Guest]

Either I am dense today or you are being too criptic with your posting as I
can not see the value in Table2. Because of that I do not understand how you
expect to get the query 1 results.
Query 2 looks like a crosstab query but I can not follow it also.

Please post some real data for your tables.

 

[Guest]

Hi Karl, the values are there. Thanks.
Peter

ie. the 3 values for App are: app1, app2, app3.

 

[Tom Ellison]

Dear Peter:

If my guess is correct, you want to show all possible combinations of
Company and App, showing Type only when there is one shown.

You have a table "Table 2" which lists all the possible values for App. You
do not show a similar table showing all values of Company. For that, then,
I'll use this query:

SELECT DISTINCT Company FROM [Table 1]

The next step is a cross-product:

SELECT *
FROM (SELECT DISTINCT Company
FROM [Table 1]), [Table 2]

This is the set of all possible combinations. To retrieve the Type from
[Table 1] you need to LEFT JOIN to that:

SELECT C.Company, A.App, C1.Type
FROM (SELECT DISTINCT Company
FROM [Table 1]) C, [Table 2] A
LEFT JOIN [Table 1] T1
ON T1.Company = C.Company
AND T1.App = A.App

I have added aliases to condense the length. This completes Query1 as far
as I can see.

Making a crosstab of this is pretty straight forward. Start with the above
query (assuming I've got that right) and just use FIRST() as the aggregate
function for the values of Type.

Tom Ellison