QUERY: Output Data from multiple records

[Carol]

Howdy!
Challenging question (at least for me it is):

I have a table in Access where some of the records are duplicates. In the
cases where the rows are duplicated (only the ID is the same, the other data
elments are different), I want to display the ID and the data elements from
BOTH rows.

Example: My table has the following values:

ID# Bank# Zip
1234 999333 43087
1234 998111 43084
3333 656111 43001
3333 659999 43023
9999 453333 43073

My Output would look like this: (if this is at all possible)

ID# Bank # Bank #1 Zip Zip1
1234 999333 998111 43087 43084
3333 656111 659999 43001 43023
9999 453333 - 43073 -

Can anyone help me outon this one? THANK YOU!

 

[Dirk]

Could you be so kind as to show the exact query (SQL) you have been using?

"Carol" schreef:

 

[Carol]

HI there. I have no SQL because I don't know how to code it to get the
desired results. :-( I'm starting from scratch.

 

[Dirk]

Well it is not exacly possible to display it exactly the way you want it to
be displayed as far as I know. What are you trying to represent in the table.
It's possible that respresenting the data in a different structure makes it
more usable. ID's for example are supposed to be unique when they are Primary
Keys.
Could you elabirate a bit on what the table is for?

"Carol" schreef:

 

[Carol]

The data in the table will ultimately feed a form created in WORD.
We need only one distinct record per line so that only one page (form) in
WORD prints out. If I use the data as is, when I do the merge in WORD - I
get 2 forms per record (i.e. 2 pages print out for id 1234, instead of 1).
The 1 page that I need to print out would show both the bank # and zip code
values.
Does this make sense? thx

 

[John Spencer]

Assumptions:
There are no more than two records that are duplicated for any one ID#
If there are duplicated ID#, the Bank# differs.

The following UNTESTED SQL may give you what you want.

SELECT DISTINCT T1.[ID#], T1.[Bank#], T1.ZIP, T2.[Bank#], T2.ZIP
FROM YourTable as T1 INNER JOIN YourTable as T2
ON T1.[ID#] = T2.[ID#]
WHERE T1.[ID#] IN
(SELECT YourTable.[ID#]
FROM YourTable
GROUP BY YourTable.[ID#]
HAVING Count(YourTable.[ID#]) > 1)
AND T1.[Bank#] < T2.[Bank#]