Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13

    Question Need data to auto fill from another table based on relationship


    I'm trying to create a database for creating Work Orders. My company performs maintenance & other construction for several property managers, so I have customers that have several properties. My goal is to have a system of creating printout WOs to hand off & to keep track of the status & who each WO is assigned to.
    I have 4 tables: Customers, Properties, Vendors, & Work Orders

    The customers table has customer names & basic contact info
    The Properties table has property address along with customer names that can be selected in a dropdown box.
    The Work Order shows the property, customer, description, vendor, due date, status, ect.

    I'm wanting the customer field to auto-fill each time I input the property, so that I don't have to refer to the other table each time to know which customer owns which property.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The Property combobox on Work Order form can be a query that joins the property and customer tables. The customer info can be columns of the combobox. They can be visible or not visible. Then textboxes can reference column index of the combobox with an expression in ControlSource.

    =[cbxProperty].[Column](x)

    Column index begins with 0. If the customer name is in column 3, the index is 2.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    Quote Originally Posted by June7 View Post
    The Property combobox on Work Order form can be a query that joins the property and customer tables. The customer info can be columns of the combobox. They can be visible or not visible. Then textboxes can reference column index of the combobox with an expression in ControlSource.

    =[cbxProperty].[Column](x)

    Column index begins with 0. If the customer name is in column 3, the index is 2.

    OK, I'm not quite understanding. Let me give you more details.
    In the Work Orders table, I have the 2nd & 3rd columns headed "Address" & "Customer".
    In the Properties table, I have the 2nd column headed "Address" & 6th column headed " Customer". The customer field already has a combobox that pulls from the Customers table.
    In the Work Orders table, I have the Address field working as a combobox to pull up the address. I'm wanting the Customer field to show the customer once the address is selected.
    I think you may be understanding me correctly already, but can you give me more info? Where am I supposed to type the expression? I'm trying to put the data correctly in the Field Properties under Lookup, but I'm not sure how to properly fill in everything.

    Sorry, I'm pretty new at this & all the resources I have to build this are limited on the more detailed aspects of Access.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't know enough about the data structure. How are Address and Customer related? If the customer is dependent on selected address then why is there a combobox to select customer?

    The expression I suggested would go in the ControlSource property of a textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    Quote Originally Posted by June7 View Post
    I don't know enough about the data structure. How are Address and Customer related? If the customer is dependent on selected address then why is there a combobox to select customer?

    The expression I suggested would go in the ControlSource property of a textbox.

    The combobox for customer is on the properties table & pulls data from the customers table. I don't want to have a combobox for customers in the Work Orders table. Rather, I want to use the combobox to pull up the property & the customer fill in automatically.

    I'm not seeing the "ControlSource" property. I see "Row source". I'm assuming that's where the expression goes.

  6. #6
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    Also, does the expression go into the control source for the Address combobox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, the RowSource is property of combobox. This would be a query that includes Properties and Customer tables.

    The expression referencing column of combobox would be in the ControlSource property of a textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    OK. I'm really struggling here. By creating a query for this, does that cause my Work Orders table to have the autofill capability or does the query become the data source to build Work Orders?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The combobox is just to select the desired Property record and save PropertyID into WorkOrders record.

    The combobox dropdown list can be created by an SQL statement or by reference to a table/query object in the RowSource.

    The columns in the combobox depend on fields retrieved by the RowSource property. Columns can be hidden or visible.

    If I understand correctly, Property has direct relationship with Customer.

    Make the combobox RowSource a query that includes both tables.

    Use textboxes to reference the columns of combobox to automatically display the related info, in addition to displaying the data in columns of combobox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    So I'm trying a few things here. I went into the Row Source for Address Lookup & selected to use a query. When I try to use both tables: properties & customers, I end up with duplicates that don't match up properly in the query. But when I use the Address & Customer columns just from the properties table, the query looks correct. It lists all of the properties with corresponding customers. However, now my Work Orders table pulls up the properties key rather than the address from the combobox & I still can't figure out how to make anything show up in the customer column.

    The lookup Row Source now reads "SELECT Properties.Address, Properties.Customer FROM Properties ORder BY Properties.Address;"

    I think I'm getting closer, but I'm not fully understanding how the tables use the data that I'm inputting into it to predict what changes will occur.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why doesn't that RowSource have Property ID field? Shouldn't you be selecting the property and saving it's ID to the Work Order?

    I assume each property has only one associated customer.

    A RowSource with both tables would be like:

    SELECT Properties.ID, Properties.Description, Customer.CustName, Customer.Address FROM Properties INNER JOIN ON Properties.Customer = Customer.ID ORDER BY Properties.Description;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    I don't really care to have property ID on the Work Orders. I guess it doesn't hurt to have it on the table, but it's not something I'll use. For the most part, each property has only one associated customer. There can be exceptions: I would usually invoice the property manager directly for a maintenance request, but sometimes if its a full remodel or a large cost, the manager would have me work directly with their customer & therefore bill the owner rather than the manager. So it is possible to have two customers for a property. I can work on this detail later. The billing will come out of Quickbooks, so not too important to worry about this detail when creating customers.

    When creating the query, I have the Customers & Properties tables showing. I only need the Address & the Customer (that is correlated with the Address). Do I make one field the "Address" with Table "Properties" & create an expression for the other field, tying the 2 tables together?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am confused. Going in circles. Head is spinning. Provide db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Looks like you don't have a normalized db design here. Your request implies saving the same data more then once scattered over many tables. If you have full control over the db design I advise you to normalize it, honestly. It will save you from much trouble later on.

  15. #15
    amorphous is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2014
    Posts
    13
    I have full control. It's just me building this thing. This is my first time ever building a database. I've been using different resources friends gave me to start building it. I've always been pretty good with excell, but trying to work through the learning curve of access. I get thrown off by some of the terminology & some of the basic, common since steps aren't common since to me yet. Let me know what you mean by normalizing. I would prefer to do this correctly. How should I go about providing the db. I tried to upload some screenshots, but it was wanting me to zip them & I didn't have time at the moment. I guess I'll try to zip the db & see how it does uploading. You'll see that I have useless things in here. I've been just testing different things I'm learning about. The main things I have right now that I'm working with are the tables. If I can get them functioning efficiently, I can probably figure out the rest.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-01-2014, 02:11 PM
  2. Replies: 6
    Last Post: 07-12-2013, 01:07 AM
  3. Replies: 2
    Last Post: 12-18-2012, 11:41 AM
  4. Replies: 5
    Last Post: 01-20-2011, 11:36 PM
  5. Replies: 1
    Last Post: 12-09-2010, 08:29 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