Merge Multple Records into 1 1

[lotharious]

I am making a report that has to be in a certian format.

I have a table that holds multple records of 3 different types. I need to merge this data into a lesser number of records.

I have made seperate queries to pull out and sort the data for each type.

Now I need to put those queries together. There is no linking data, so I need to make one. The data in the records is completely irrelavent.

So basically what I need to end up with is
Type A|Data A1|Data A2|Type B|Data B1|Data B2|Type C|Data C1|Data C2

The number of records from A, B and C will differ at all times so I cant leave out the data..

I have done this before in VB, but that is not an option in this case. I have to have it all done within the query.

 

[PHV]

A starting point here ? faq701-4233

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

 

[lotharious]

Ok, Guess i have never used vb code like that.
I at least dont remember every putting a function into a query.

 

[lotharious]

Ok, I dont see how that FAQ is going to help me.

Is there a way to make a incremental exression in a query? So each record would come out with a
1 | Data
2 | Data
3 | Data

basically enumating the records?
From that I can link the seperate queries and should be good to go?

 

[lespaul]

maybe something like:

SELECT DISTINCT TypeA, DataA1, DataA2, TypeB, DataB1, DataB2, TypeC, DataC1, DataC2
FROM qry1, qry2, qry3



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lotharious]

No, the select distinct does not work for this. It still merges all the data into multiple records unfortunately.

 

[lespaul]

maybe if you provide some sample data and your expected results we will be better able to help you.

 

[lotharious]

Les, the data doesnt make one bit of difference
Pick any 5 tables you have in your database, and put each record from those 5 into a single query.
Making it so that each first record from the tables show up as 1 record in your query, Second, third and so forth, until you are out of data.

This would be easy, if I could make a query autoincrement the records giving me the record number in the query, thus being able to link each query via that recno...

 

[dhookom]

lotharious,
Maybe if you would provide some sort of justification or need for a query like this, it would be easier to understand. Seems that what you are asking is similar to pasting records into an Excel spreadsheet. Then moving over to the first available/empty column and pasting the next table beginning in row 1 again. You apparently want to repeat this for five tables.

There are lots of posts here and elsewhere that show how to use a subquery to create a ranking value. The ranking value could be used to join your multiple queries together. I expect this might be very slow with a decent number of records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

I am making a report that has to be in a certian format.
The report will query the database and the data must be all in a single query set.

The Report will look something like

Type A Type B Type C
-------------------------------------------------
DataA1|DataA2 .. DataB1|DataB2 .. DataC1|DataC2
...

Basically that is how we use to do it with excel. We would do excel queries, and the data would be put right in the correct columns. Now however that is not an option.

I have not done ranking before, at least knowing that was what is was called. I will search on that as well.

 

[dhookom]

Since this is for a report, have you considered an unbound main report and multiple subreports side-by-side in the detail section?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

No I had not considered that and now I feel a we bit stupid for not considering it.