Combining Data – Adding the improbable to the impossible. 1

[creativeimages]

Can Access, in a query, look at more than one record in a table? Several of my source data have two, three or four records (lines of text) that make a single record of information – this approach works well when the multiple lines of text are printed; the printed text shows as related information on the page.

 

[Hap007]

Dear creativeimages,

Sure, this could/can be done.

Let's hope that you have 2 fields in your record layout to identify each record, a primary key to identify the group of records and a counter or record type to identify line 2, line 3 etc..

Now Build 4 queries.
1) Query to get all first line records
2) Query to get all second line records
3) Query to get all third line records
4) Query to get all fourth line records

Now build a fifth query that uses the above 4 queries and create your output. Makesure to set relationships up so that the main(first query) returns records regardless of data from the other queries, and only returns one row.

I know this will work if setup right.

Hope This Helps,
Hap..

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[M8KWR]

is this for reporting purposes?

If it is then this would be possible via about 2 queries.

One query to retrieve the main information less the fields that have multiple lines of information. The best way would be to group the filter, so duplicates are merged together.

Then in the other query, relate the first query to the same table to get the extra information that you require.

Create a report using the first query, then a sub report linked to the second query, and insert this into the main report and hopefully this should look like how you want it.

Hope this helps, and i have understood what you require, it not let me know, and i'll have another go at it...

 

[creativeimages]

Hap..

Your approach is something that I had not considered. But, I guess that is what I am doing in a-round-about way. I import the data to Excel, in one (A) column. Then, I create formulas that move A2 to B1, A3 to C1 and A4 to D1. Sense all the “records” begin with a dash, after I copy the sheet to itself as special paste for values, I sort the sheet and remove all the extras. Then import the Excel spreadsheet to Access.

What do you think is easier and faster?

 

[creativeimages]

M8KWR

The data will be part of a complex report. Of the four lines of text that make the public “record,” there are no duplicate fields in the multiple records in the public report. The only connection of the data is the location of the line of text, after the line of text, after the line ...

creattiveimmages

 

[Hap007]

Dear creativeimages,

Well, it sounds like your method works, but you have to get involved every time the data changes and you want to rerun the query or report.

My way is a permanent fix. Once you build the needed queries, then whenever the data changes, the report is good without any manual intervention.

Now I do not know your skill set, but it would take me less than 30 minutes to create all five queries. So in 30 minutes or less, I would have a permanent solution that does not ever require manual cutting and pasting.

Or, you could just redesign your table, but that was not the original question.....

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121