Results 1 to 5 of 5
  1. #1
    Rlagerst is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    3

    Question setting up Access 2007 Query with 3 tables

    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


    Click image for larger version. 

Name:	Clipboard01.jpg 
Views:	7 
Size:	62.2 KB 
ID:	37014

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a draft data model from Barry Williams' site. You may get some ideas from it. You can adjust as needed to suit your purposes.

    Why would you name a table that deals with Property formHeader?

    I recommend you download this tutorial on tables and relationships. It's a quick introduction to database design and lets you work through an example to get experience with the process.

    Good luck with your project.

  3. #3
    Rlagerst is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    3
    Thank you for responding to my post. The table was named formHeader by default from the data I obtain. I thought it would be best to leave it as named.
    I will look over the info you posted. Appreciate your taking time to answer.
    Rich Lagerstedt

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I really recommend that you work through the tutorial mentioned in my previous post.

    And I'm providing this link to several articles in various formats related to Database Planning and Design It may serve as a reference as you proceed with your project. The knowledgeNuggets from BA-Experts can help put various activities into perspective.

    Good luck.

  5. #5
    Rlagerst is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    3
    I am looking over the links you send a short while age. I will be delving into this one as well.
    Much appreciated!
    Rich Lagerstedt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Setting the tables in the Query Window
    By George in forum Queries
    Replies: 5
    Last Post: 03-16-2017, 08:50 PM
  2. Replies: 8
    Last Post: 07-11-2014, 12:36 PM
  3. Replies: 1
    Last Post: 05-08-2013, 07:08 PM
  4. Nested Query Question Access 2007
    By databased in forum Queries
    Replies: 5
    Last Post: 10-15-2010, 07:22 AM
  5. Access 2007 question?
    By NewOrder in forum Access
    Replies: 3
    Last Post: 08-08-2010, 06:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums