Results 1 to 11 of 11
  1. #1
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6

    Need to have a single table automatically update with data from multiple tables

    I'm having a hard time explaining it so I'll just give an example of what I want.

    Employees A, B, and C each have two tables. Each has both an Invoice table and an Order table with the exact same fields. I want to keep each table separate so that each employee can only change their own table and I can put different drop downs, etc. in each employee's table. The difficult part is that the boss needs to be able to look at one table with all of the Invoices and run reports on it. Can I do this? Or are there any other good options that come close to this idea? Thanks.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You could easily have just two tables and limit EmployeeA to just see EmployeeA entries assuming there is an Employee field in the Invoice table.

  3. #3
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6
    Quote Originally Posted by RuralGuy View Post
    You could easily have just two tables and limit EmployeeA to just see EmployeeA entries assuming there is an Employee field in the Invoice table.
    Then I would not be able to have a different table design for each employee right? I want to keep all the fields the same but want different dropdowns for each employee since they each work on their given departments. It would save significant time.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're not using LookUp Fields are you? http://access.mvps.org/access/lookupfields.htm

  5. #5
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6
    If I understand correctly (pretty sure I do) then no. I just want to use drop downs and automattically fill certain fields to make inputting data easier.

    I'm thinking that I can create a "Master" table with foreign keys in it coming from the other tables, but I'm having a bit of trouble with that too. Is there a way automatically make my primary keys (auto number) from Table A and Table B sync(?) so that a record in Table A would never have the same primary key as Table B?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Lookup Fields are at the table level. ComboBoxes (dropdowns) are controls on a Form. Every table should have its own PrimaryKey (hopefully and AutoNumber) and you do not care if it matched another table's PK.

  7. #7
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6
    Ok, if you haven't figured it out by now, I'm kinda a noob lol.

    I was doing everything at the table level and not using any forms to input data. Is this a huge problem? The reason I wanted to do that is because the employees are all used to spreadsheets and need to be able to see the data that they've entered and go back to edit it often.

    I guess I was trying to use Lookup fields. In the design view, I was clicking the Lookup tab and then selectin drop down. So what should I be doing? Can I easily get a form that functions the same as inputting data directly into the table for the front end user?

    Thanks for all your help.

  8. #8
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6
    So how can I do this? I need the data in a spreadsheet format that shows the old data and can be edited by the front end users. I also need to keep the data as consistent as possible (So I don't have one person using an abbreviation and another spelling it out). I think the only way to do this is with drop downs. I also need to be able for the boss to combine the employee spreadsheets and run reports on them. So what are my options? Can I do what I need to in Excel or a combo of Excel/Access.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I see no reason you cannot do it completely in Access. A Form can display in Single Form View, Continuous Form View, and Datasheet View. Both Continuous Form and Datasheet Views can look like a spreadsheet if you want them to.

  10. #10
    t_roy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    6
    Ok, yea I need to do it in data sheet view. I haven't done much with forms. I can make the drop downs show up in the datasheet view just like I had them on the table level right? So the best way will be for me to have one table and give each employee a different form right?

    Thanks a lot. You saved me a lot of work that would've had to been redone.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Assuming you have an EmployeeID field, I see no reason to have more than one form. You would simply change the RecordSource query to reflect the EmployeeID you want.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-14-2012, 02:01 PM
  2. Replies: 1
    Last Post: 08-30-2011, 07:35 AM
  3. Multiple UPDATE in single query
    By nishezaman in forum Access
    Replies: 0
    Last Post: 12-13-2009, 01:40 AM
  4. Table related to multiple tables by single ID
    By MrTumnus in forum Access
    Replies: 1
    Last Post: 11-17-2009, 02:05 PM
  5. Replies: 8
    Last Post: 11-04-2009, 04:22 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