Help with Table Aliases (?)

[susieqtotallynew]

I am new to SQL and Access, so I appreciate your patience.

I have a table like the following:
ID Adddress Type ID Address 1
1 2 123 Drop St.
1 4 456 Lemon St.
2 2 567 Jump St.
2 3 789 Pete St.
2 7 678 Frustrated St. (I'm there!)

I would like to produce a query result that lists address type 2 and 4 for each ID number. In some cases, ID type 2 or 4 will not exist, as in the case of ID 2, where 4 does not exist.
Is it possible to do this with one query?
Thank you again.

 

[HarleyQuinn]

Yep, you can do this with a simple OR construct in the WHERE clause. Something like (assumes Address Type 1 is a NUMERIC column):

SELECT ID, [Address Type ID], [Address 1]
FROM YourTable
WHERE [Address Type ID]=2 OR [Address Type ID]=4

IF you need any further explaination of this query please post back.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[dhookom]

Another alternative that might be a little easier to maintain is:

SELECT ID, [Adddress Type ID], [Address 1]
FROM YourTable
WHERE [Address Type ID] IN (2, 4);

Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

Should have clarified a bit...I'm hoping to list this on ONE line.
Thanks again...May not be do-able? You all are a treasure!

 

[Golom]

For this very limited case

SELECT ID, 

       (Select [Address 1] FROM YourTable As Y1
        Where Y1.ID = Y.ID AND 
              [Address Type ID] = 2) As [Address 2],

       (Select [Address 1] FROM YourTable As Y2
        Where Y2.ID = Y.ID AND 
              [Address Type ID] = 4) As [Address 4],

FROM YourTable As Y

The above is however, really bad practice because it hardcodes data values into SQL DML statements and they become a bear to maintain. It will also probably be slow because it involves two coordinated sub-queries.

 

[dhookom]

Should have clarified a bit...I'm hoping to list this on ONE line

How about providing exactly what you mean by "ONE line"? Can you provide and example of the output so we can better understand your needs?

Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

Of course.
ID AddTypeID AddType Add AddTypeID2 AddType2 Add2
1 2 Home 123 Lemon St. 5 Work 456 Drop St.

 

[dhookom]

If this must be done in a query, check out faq701-4524 faq701-4233.

If you can live with a report, consider using a multiple column subreport to display the addresses in a main report of unique IDs.

Duane
Hook'D on Access
MS Access MVP