Results 1 to 15 of 15
  1. #1
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    7

    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  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
    16,716
    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
    7

    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
    7
    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
    16,716
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    7
    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
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    7
    Quote Originally Posted by ridders52 View Post
    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


    Okay - I have created tblStaff and used Append queries to add all staff form the individual team tables. The PK did indeed avoid duplication.
    My next step for this is to be able to get my head around UPDATE and DELETE queries. Each team table will be updated monthly - some staff may join, change their work criteria or leave. What I need to happen is for staff to remain in the tblStaff if they belong to at least one team, but to disappear once they have left the organization. I am guessing that if all team admins have removed a staff member from their lists on departure, that this will result in the UPDATE removing them from the tblStaff. If not, how do I need to structure me DELETE queries? I am thinking I will need a separate table that is used to list staff that are leaving completely to run a DELETE query regularly to tblStaff.

    In terms of tblStaffRoles, how do I number my key for this? Do I want the numbered keys to represent all the possible combinations of the fields? In other words, if I have 5 staff with responsibilities as follows.....

    1: Theme Lead
    2: Theme Lead
    3: Workstream Lead
    4: Workstream Lead & Theme Lead
    5: Workstream Lead & Theme Lead

    ....then this represents 3 different unique role IDs? Is that right?

    One of the fields will be the team (theme area) to which staff belong. Would I need to repeat the step shown immediately above for all of these areas to establish all the possible Role IDs? In other words, Theme Lead in Team A, Theme Lead in Team B and Theme Lead in Team C will be 3 roles, and then repeat with additional responsibilities to create further roles?

    Any advice gratefully received!

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    I'd forgotten about this as it over a month ago....

    Correct. Your table tblStaffRoles needs a RoleD (autonumber PK field) and a RoleName field
    It will need 3 records but more can be added as and when needed


    You need a separate table tblTeams to cover A/B/C etc as necessary with fields TeamID, TeamName


    Then in tblStaff , you need 2 fields for RoleID & TeamID.
    These are 'foreign key' fields linked to the PK fields in the other 2 tables with 1 to many joins
    In the data entry form you need 2 combos with row sources based on tblStaffRoles & tblTeams
    This will allow you to select any combination of job roles & teams.


    The same idea can be used for other likely itemss e.g. qualifications, JobGrade etc
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    7
    Quote Originally Posted by ridders52 View Post
    Hi
    I'd forgotten about this as it over a month ago....

    Correct. Your table tblStaffRoles needs a RoleD (autonumber PK field) and a RoleName field
    It will need 3 records but more can be added as and when needed


    You need a separate table tblTeams to cover A/B/C etc as necessary with fields TeamID, TeamName


    Then in tblStaff , you need 2 fields for RoleID & TeamID.
    These are 'foreign key' fields linked to the PK fields in the other 2 tables with 1 to many joins
    In the data entry form you need 2 combos with row sources based on tblStaffRoles & tblTeams
    This will allow you to select any combination of job roles & teams.


    The same idea can be used for other likely itemss e.g. qualifications, JobGrade etc


    Hi,

    Thanks for your reply, that's really useful. Can I just check with you - in my tblStaffRoles table at the moment I was thinking of drawing data from the individual team tables that administrators currently update. This would include checkboxes for Theme Lead, Workstream Lead, PPI Lead and Training Lead, which is important information we need to capture. Some people are none of these, they are simply academic researchers or support staff, which is currently captured under the tblStaff in the Member Status field - perhaps this would be better under the tblStaffRoles remit? Otherwise not everyone will have an entry in that table and that doesn't seem to make sense.

    I will add tblThemes to the mix. Now then - most people work in one Theme, but some can work in two and VERY OCCASIONALLY three. So, should this have Theme ID (PK), Staff ID (FK), Role ID (FK) and then up to 3 fields for the 1-3 Themes that people can work in at any given time?

    I feel I am getting my head round Access more now! But I leave that to your better judgment....

  13. #13
    periolus is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Dec 2018
    Posts
    7
    Click image for larger version. 

Name:	AdminDataInputTables.jpg 
Views:	7 
Size:	173.1 KB 
ID:	36898Click image for larger version. 

Name:	tblStaffRoles.jpg 
Views:	7 
Size:	164.4 KB 
ID:	36899

    I attach two screenshots to illustrate this a bit more clearly. So, one is for the tables into which individual Theme (research team) Administrators will enter staff details to be updated to master tables tblStaff, tblStaffRoles and now possibly tblTeamRoles.

    Administrators will use checkbox fields to denote special roles that some staff have allocated to them - Theme Lead, Workstream Lead, PPI Lead, Training Lead. It would be great if this could simply be appended straight to tblStaffRoles. Would that then be a case of just setting up all the combinations with a Role ID and then just linking that to a Team ID to show where they actually work?

    I also attach my current design for tblStaffRoles. For staff working in multiple teams, how do I best structure my fields?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    First of all, I STRONGLY recommend you do NOT use spaces or underscores in object names (e.g tables/queries/field names) as it will make your life MUCH easier ... e.g. StaffID NOT Staff ID, qryAppendStaffPPI instead of Append_PPI_to_tblStaff etc


    Using spaces requires objects to be wrapped in []
    Underscores are less of a problem but hard to read


    You should not have separate tables holding similar info - combine all Theme tables into one & add an extra field to indicate ThemeType

    Administrators will use checkbox fields to denote special roles that some staff have allocated to them - Theme Lead, Workstream Lead, PPI Lead, Training Lead. It would be great if this could simply be appended straight to tblStaffRoles. Would that then be a case of just setting up all the combinations with a Role ID and then just linking that to a Team ID to show where they actually work?
    The 4 boolean fields for different Lead roles are an unnormalised structure
    If staff can only have one role (or none), use a field LeadRole linked to another lookup table with the 4 types
    use combos instead of checkboxes (assuming staff have only one role) as previously described.
    Not sure what you mean by appended? You mean entered?


    If staff have multiple lead roles, then you need another table with a one to many join. One record per role.
    You can probably see the structure that is emerging here
    If a member of staff can have more than one of anything, it needs to be in a separate table

    The same type of comment applies to multiple teams
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry to jump in so late, but wouldn't the use of a union query would have solved the original problem in creating the master table and removing duplicates. All that would have been needed was the creation of some "translation" queries to be used in the union instead of the original tables to ensure that all 9 or 10 tables have the same fields.

    Cheers,
    Vlad

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

Similar Threads

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