Best way to append a sequence of rows in to separate columns

[Benjam26]

I have been building a database which runs off of multiple append queries from a CSV file.
The data is split into 20 tables depending on a Record Type given to each row.


Once the data is split I am then putting the necessary information back together into a final table to make all the data received easier to read and manage.

A couple of the parts have multiple entries for the same column however. For example owners. If the item has multiple owners two or more entries are shown in the owner table and are identified with a sequence number.
Something similar to this:

Reference Number	Sequence	Owner
011181662	1	John Smith Realty
011181662	2	City Towers Ltd
01181500	1	ABC Mgmt

The Owner field will be followed in the main table by their address, which comes from a separate table, the addresses are also split by the same sequence number. So what kind of things can I do to differentiate between the two owners in sequence 1 and 2? Is a criteria of [tblONM]![Owner Sequence Number] = 1 appropriate?

Thank you.

[Bulzie]

Not sure if following. Each Owner has 1 address or multiple? So you have 1 table with Owner info which includes their address?

[Benjam26]

Yeah I definitely could have explained myself better.

Each property has its own reference number with the owners and other information being split over multiple rows which I want to clean up and show as a single row.

Some properties are owned by two or more entities so one owner might have 50% ownership and two others might have 25% each.

Therefore the property will have one address, which will be the main address used in the table. One property address per row in the main table. Then in the columns I will have the owners of the property and their address.

What my original question meant was that for John Smith Realty (sequence 1) I then want to show their company address, then sequence 2 owner followed by their company address. so the [tblONM]![Owner Sequence Number] = 1 criteria for the property fields would show the correct address.

I think I answered my question with my original post with a usable solution, I just wanted to check that it was the safest way to do this as I will be going through 100s of property records at a time.

[Gicu]

Use the OwnerID (or name) and Sequence together (double join) in a join when building your query.

Cheers,

[orange]

Benjam26,

I suggest you step back and tell us in simple English what you are dealing with and what you are trying to accomplish.
Forget rows and sequence etc. for the moment.
You have Properties and Owners. Now what? You want some report(s)?

[Benjam26]

Benjam26,

I suggest you step back and tell us in simple English what you are dealing with and what you are trying to accomplish.
Forget rows and sequence etc. for the moment.
You have Properties and Owners. Now what? You want some report(s)?

I have property ownership records which have details such as the property location and the owners information and location.

The records I receive come in a text file which is collected from 20 different tables. I have built append queries to take the text file and recreate the original 20 tables as each table has a different number of fields.

Now that the data is split into its correct table I wish to recreate usable tables from this data, consisting of the property, owner and information relevant to them. This is currently using 7 of the 20 tables.

The query I have created gives me the results I want until it gets to a certain part where the data has been split as shown in the table below.

LINC Number Sequence Number Long Legal Text 0018591132 1 PLAN C 0018591132 2 BLOCK 29 0018591132 3 THAT PORTION OF LOT 12 LYING EAST OF THE WEST 4.6 FEET THROUGHOUT OF 0018591132 4 SAID LOT 12 AND ALL OF LOTS 13 TO 20 0018591132 5 EXCEPTING THEREOUT STREET WIDENING ON PLAN 1665LK

I want to have each line of the Long Legal Text in its own field which I have listed as Legal Text 1, Legal Text 2 etc.

The problem I am having is when trying to append this to the new table.
I thought it would be something along the lines of;

Field:	Long Legal Text	Long Legal Text
Table:	tblLEG	tblLEG
Sort:
Append to:	Legal Text 1	Legal Text 2
Criteria:	[tblLEG]![Sequence Number]="1"	[tblLEG]![Sequence Number]="2"

However when I save the query it removes the criteria and adds it to a field at the end of the query as;

Field:	[tblLEG]![Sequence Number]
Table:
Sort:
Append to:
Criteria:	"1" And "2"

I have tried putting the remaining criteria in to Or column but that doesn't help much either.

I have a few other fields I would like to do something similar with but I won't move on to them until I can get this part working first.

Thank you for your help and any suggestions.

[Gicu]

I would recommend against what you are trying to do as it goes against the basic principles of proper database design (what if you in the future get a record with nine owners). You can get your data the way you describe it in query in couple of ways. You cane create multiple saved queries for each sequence (tblLEG1 for sequence 1, tblLEG2 for sequence 2, ....) and linked them to your main query using Left Outer Joins. Or you can create a couple calculated fields where you join the multiple records from each multiple=record tables into one field using custom functions (look at http://allenbrowne.com/func-concat.html or I have another version Join From Array http://forestbyte.com/vba-code-samples/).

Cheers,

[Benjam26]

Thank you for your reply Gicu.

I actually do already have instances where there will be multiple owners as a fair few of these properties are split in to 50/50 or even 25% ownerships etc, so although the Legal Text part will always be the same once I get to the owners I can see that I will be getting in to some problems.
I will read through your suggestions now, thank you!
I would rather not create multiple queries for each of these as it will get very messy down the line once I start getting in to the history of the properties which I was planning to create a separate table for, the sequences for those easily get into 30 or so differing results.

Thanks!

[orange]

Benjam26,

Can you post a graphic/screenshot of your tables and relationships?
Or a copy of your database with anything confidential removed.
I take it that you are in early development and have some sample data available.
If so, that would be helpful to readers who are willing to assist.
Always better to have a "hands on" sample when discussing options.

Good luck with your project.

[Benjam26]

Test_Database.zip is a copy of my database, this is just a sample of the data of just two properties. I have changed some names around from the original but this is all public records so nothing confidential.

I have had to remove the primary key which I had set up as the Title Reference field in tblLTL but it appears that it was having some struggles with some duplicates, although I specifically chose that table to hold the Title Reference key as LTL is only ever used once as a record type per property and is always the first line of a new record.

I have attempted to create something like the concat function suggested by Gicu but as the two fields I wanted to use it with are in the same table it wouldn't run properly, I am currently trying to find a solution for it.

Thank you for your help. This is a little out of my depth as a project but its fun to be trying new things and I am definitely learning a lot on the way.

[orange]

Benjam26,

My opinion is that you are way too deep into physical Access. You need a design that you will get to via analysis of the "requirement". I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary identified in this link(Database Planning and Design).
If you work through the tutorial(s) you'll see that he starts with a clear description of the "business" to be supported with a database. The process he uses will lead you to a design/data model that you can test before getting into physical Access. It is much easier to adjust, test and adjust/refine things on paper. And it will save you time for your project. If you spend 45-60 minutes working through a tutorial, you will be able to apply what you have learned - and it can be used with any database.

Also review the "stump the model" article that is also in the linked material. The data model, once tested, will become a blueprint for your database.

As for Access, it is better to not have embedded spaces in field and/or object names.

Good luck with your project.

[Benjam26]

Thank you for your suggestion! I will work my way through those tutorials this week and see how I progress.

Thank you for the naming tip! I now remember being told that ages ago, oops!

Thanks again