Results 1 to 9 of 9

Make table query issues

  1. #1
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    4

    Make table query issues

    Hi,



    Please can someone help! I am building a staff list database and currently have 9 tables showing staff that work on particular project areas. Some staff work on more than one, but we'll come to that in a minute. All I want to do is combine the 9 tables as they look into one long table to show everyone that works across our organisation. When I used the Microsoft Help pages to do this, I followed the instructions and ended up with tables of data side by side in a very wide table, and the data was scrambled for most of it.

    One issue - I don't have a primary key set at the moment - it was the first name field for each table, but it was suggested I remove it during a problem i had with a different query type.

    So - next I tried lumping fields together in the query design, seperated by '+' operands, so that fields would appear by table in successive order, but this collected them into 'Expr1', 'Expr2' etc. for each table and that's not what I want.

    Can someone please guide me through step-by-step so that I can just have one table that flows with the same column headings that the individual tables have?

    Another thread will be then how I automatically deduplicate data in the master table I have created.

    Any advice gratefully received!

    Many thanks,

    Periolus

  2. #2
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Did you keep the original 9 tables as it will be less work to start again and not create duplicates.
    You will definitely need a primary key field but staff name isn't a good PK as it could easily be repeated.
    Whilst I'm sure that the table design could be improved, it is likely that you will still need more than one table.
    Suggest you give more details about your current structure,
    Place your 9 tables in the relationships window and if appropriate show any links between them
    Or make a copy of your database, remove all confidential data, compact, zip and upload so you can get more informed advice.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,111
    In addition to the advice and suggestions Colin has offered, I recommend you give us a description in plain English of
    -what your tables represent and
    -what (the general process(es)) you are trying to automate with this database.
    Good luck with your project.

  4. #4
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    4

    Re: Make table query issues

    Yes, all 9 tables are in use. The idea is that the individual tables get updated by the teams responsible for them, and then once a month or so I run a query to combine them all into a master table. I have attached a screenshot (jpeg) of the tables with their fields as they exist. Over time, once we have all the data in, I will create queries so that we can look at specific areas.

    I hope this helps! The tables are a bit of a mess - some have keys, others not.



    Quote Originally Posted by ridders52 View Post
    Did you keep the original 9 tables as it will be less work to star again and not create duplicates.
    You will definitely need a primary key field but staff name isn't a good PK as it could easily be repeated.
    Whilst I'm sure that the table design could be improved, it is likely that you will still need more than one table.
    Suggest you give more details about your current structure,
    Place your 9 tables in the relationships window and if appropriate show any links between them
    Or make a copy of your database, remove all confidential data, compact, zip and upload so you can get more informed advice.
    Attached Thumbnails Attached Thumbnails DB example relationships.jpg  

  5. #5
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    4
    Orange - thanks for your reply. In plain English, I want to be able to combine the 9 tables shown in my screenshot into one master table. Each of the 9 individual tables shows staff, with various attributes, working in different areas. I just need to combine them all so that they read as one table for a 'master list'.

    THEN - I need to be able to automate a deduplication tool to sort out the staff that work in multiple areas, and so will have entries in multiple tables. For the master list, I only need them in once to show they work for us. That's it really.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,111
    I don't know details of your situation but on first look it appears that the structure is not representative of your needs.

    Seems to me -who I state again knows little to nothing of your environment -- that you are dealing with

    People who work on Projects that have specific "Themes". So as a first cut at a draft

    Person --->PersonWorksOnProject<---Project<---Theme

    I don't know what Direct/Indirect represent.
    Organization could be a table where an Organization categorizes Person. That is Person A, B and G are members of Organization 120.

    Good luck.

  7. #7
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Yes the tables are indeed a mess with lots of duplication.
    There are also ten of them.

    I think you need two tables (possibly more) each containing one type of info.
    Each table needs a PK field

    1. TblStaff with StaffID (PK) and probably the first 8 fields from the Core table.
    Append all records from the Core table or whichever has all the staff names.
    2. TblStaffRoles with RoleID (PK), StaffID (FK) and the next 6 Core fields plus a field to denote RoleType
    Append all records from each of the 10 tables (unless this will lead to duplicate records) and as you do so populate the StaffID and RoleType
    So you may have up to ten records for each member of staff but none will be duplicates
    3. That leaves the additional fields in the cross cutting table. Need more info on these to determine how to manage them.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  8. #8
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    4
    Quote Originally Posted by ridders52 View Post
    Yes the tables are indeed a mess with lots of duplication.
    There are also ten of them.

    I think you need two tables (possibly more) each containing one type of info.
    Each table needs a PK field

    1. TblStaff with StaffID (PK) and probably the first 8 fields from the Core table.
    Append all records from the Core table or whichever has all the staff names.
    2. TblStaffRoles with RoleID (PK), StaffID (FK) and the next 6 Core fields plus a field to denote RoleType
    Append all records from each of the 10 tables (unless this will lead to duplicate records) and as you do so populate the StaffID and RoleType
    So you may have up to ten records for each member of staff but none will be duplicates
    3. That leaves the additional fields in the cross cutting table. Need more info on these to determine how to manage them.

    Thanks Colin. It's really hard for me, as I have been asked to set it up the way it is and it needs to be able to be updated easily by other people who are definitely not Access users. I see your point about condensing the data into fewer tables. 'Core' is just another work area - the table that I will need to set up as a MASTER TABLE showing ALL STAFF hasn't been set up yet. Cross-cutting describes two other areas of work, so all 9 tables are just areas of work with names specific to our organization. The difficulty might lie with the checkboxes, as a member of staff (BOB A) might be a Workstream Lead in one area of work, but not have any special status in another.

    Complicated, eh?

  9. #9
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,708
    Quote Originally Posted by periolus View Post
    Thanks Colin. It's really hard for me, as I have been asked to set it up the way it is and it needs to be able to be updated easily by other people who are definitely not Access users. I see your point about condensing the data into fewer tables. 'Core' is just another work area - the table that I will need to set up as a MASTER TABLE showing ALL STAFF hasn't been set up yet. Cross-cutting describes two other areas of work, so all 9 tables are just areas of work with names specific to our organization. The difficulty might lie with the checkboxes, as a member of staff (BOB A) might be a Workstream Lead in one area of work, but not have any special status in another.

    Complicated, eh?
    Not really - you just need to approach it systematically & not delete anything until the new tables have been checked.

    I would suggest you start by adding a StaffID PK field to each table and populating it ensuring you use the same StaffID for the same person each time
    Then create your new tblStaff appending records from as many tables as necessary to get all staff
    By adding a PK field in advance, it won't let you add duplicate records
    Similarly when you create a tblStaffRoles or tblStaffProjects as appropriate

    Before you do all of this you also should ensure you have the appropriate datatype for each field
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

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

Similar Threads

  1. Replies: 4
    Last Post: 04-07-2018, 10:55 PM
  2. Replies: 2
    Last Post: 02-17-2015, 12:01 PM
  3. query for table issues
    By IT_Charlies in forum Access
    Replies: 1
    Last Post: 02-12-2013, 03:49 PM
  4. Make Table Query Issues
    By Lazor78 in forum Queries
    Replies: 1
    Last Post: 03-01-2012, 06:26 AM
  5. Replies: 2
    Last Post: 10-27-2009, 06:09 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
  •  
Tech Forums: Microsoft Office Forums