Hi,
I am trying to construct a query for a real estate sales database. I am using MS Access 2007. I have 3 main tables, which are the seller, the property characteristics, and the buyer.
The three tables each have a common identifier, which is called a DLN. The DLN exists in each table to identify each transaction's relationship. The DLN is key to the overall transaction, and is unique to each transaction, meaning the sale from Smith to Stevens will have a certain DLN in all three tables, the sale from Hardy to Harrison will have a different DLN for that sale, etc.
The seller table can have 1, 2, 3, 4, or more sellers. The number of participants in the real estate sale on the seller's side is identified by an item called index. If a sale has 1 seller, there will be a 1 next to the seller's name. If the sale had two sellers (say husband and wife) the number 1 would be next to the husband and 2 would be next to the wife. If there was a third party selling the property, they would be # 3, and so on. This numbering then starts again for the next property sale at 1 for one seller, 2 for two sellers, etc.
The same thing is also on the buyer's side of the sale-1 for 1 buyer, 2 for two buyers, etc.
The middle table with all the property details (sale price, sale date, acreage, etc.) has a column that states the total number of sellers and a column that states the total number of buyers. If the total number of sellers is 5 then I know there will be 5 individual sellers numbered 1, 2, 3, 4, and 5 in the seller index. The same is for the buyers.
Although there can be over 5, 10, 15 or more sellers (and/or buyers), I do not need more than the first four to show up in results.
Now on to my struggle.
I want to create a form with certain fields that will show up in it, those being up to 4 sellers, up to 4 buyers, their respective addresses, and some of the property details.
My basic vision of the form is to have a block on the left with 4 spaces for the seller(s) name and address, a block on the right with the buyer(s) name and address, and a block below with the property details I want to include. I can figure out the details of the layout, so I don't need help on that part.
The database is populated by importing from an xml file. Each week I obtain an xml file and import it into the database. I do not type information into the database's individual fields.
Where I am confused is how to populate the seller fields, the buyer fields, and the property details. How do I create a query that will extract the first 4 sellers, the first four buyers, and the property details? It is the "put seller 1 in the seller 1 slot", and "put seller 2 in the seller 2 slot", etc. that I am confused by.
I am hoping this can be accomplished with basic queries, and not have to use any VBA code that I would then need to try to figure out. I have a basic understanding of Access, but by no means an expert.
Thank you for any thoughts, suggestions, or solutions.
Rich Lagerstedt