Combine 2 Access Records into 1 Excel Row?

[1stTimeOut]

From my database, I output to an Excel spreadsheet. The database shows two records, with some matching fields and some varying fields. I need Access to analyze the LABEL #s and if they match, combine the two records into one row of cells in Excel. I ran a macro using "ALT-Enter" to create a second line in a cell, and the code used was R1C1 and Chr(10).

This is what I have now: 2 rows in Excel
Qty MKT LABEL # CENTER SEQUENCE # Date 1
7 ATL abc-abc SANTA 3,4,5,6,7,8,9 11/9/2006
7 ATL abc-abc SANTA 0,1,2 8/30/2006

This is what I need: 1 row with two lines in a cell if needed
Qty MKT LABEL # CENTER SEQUENCE # Date 1
7 ATL abc-abc SANTA 3,4,5,6,7,8,9 11/9/2006
0,1,2 8/30/2006

Does this make any sense to you all?

Any help is GREATLY appreciated!

 

[SkipVought]

Hi,

You'll have to make a quer like this...

select
  A.Qty
, A.MKT
, A.[LABEL #]
, A.CENTER
, A.[SEQUENCE #] & B.[SEQUENCE #] As AllSeq
, A.[Date 1] & B.[Date 1]         As AllDat

From
  [YourTable] A
, [YourTable] B

Where A.Qty      =B.Qty
  and A.MKT      =B.MKT
  and A.[LABEL #]=B.[LABEL #]
  and A.CENTER   =B.CENTER

Skip,

_[red][/red]

 

[1stTimeOut]

Skip,
Thanks for taking time to answer my question. As I am really new to this, may I please make sure I'm on the same wavelength as you?

The two records reside in one table. How does A. and B. work for one table?

After the laughter dies down....thanks, and don't worry, I'm getting used to being the dumbbell!!!

Meg

 

[PHV]

Skip's suggestion is a SELF join using ALIAS to distinguish the 2 different instances of the SAME table.

I'd add the following criteria:
AND A.[Date 1] > B.[Date 1]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886