Results 1 to 8 of 8
  1. #1
    djclinton15 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    95

    Trouble joining 2-Tables in a query

    Hi. I’m trying to join 2-queries within a query from two different tables. Let’s start with me giving you some background on this database. This is a small database that keeps track of employees who work for me. I only have maybe 40-active employees who are presently working for me. Though this database goes back to 2017, so I have a lot of employees who have been terminated & hired over the years. We’ll just focus on those active employees, which only one employee will work for me on a certain day. Some employees only work for me on certain weekdays, while other employees will work for me on more the one weekday. I have two tables, the first table called, Employees_Info_tbl which holds all of the personal information of each employee. Along with Employees_Info_tbl which weekday each employee usually works & whether they usually work in the daytime or in the evening time. I only have just 1-employee working for me; some days 1-employee in the daytime, but I always have an employee working with me in the evening time. This Employees_Info_tbl also has a check box indicating if the employee has been terminated or if they are still actively working for me. Each employee is given a unique ID (an abbreviation of their first & last name with the first letter of their middle name. i.e. Kim L Summers = KmLSmmrs). The EmployeeID, KmLSmmrs is set as the primary key for this table.



    The second table, Employees_Entries_tbl list the employees by their EmployeeID. Along with, all of the dates each employee worked (or works), the amount of time an employee worked, & whether they worked in the daytime or in the evening time. From this Employees_Entries_tbl I created a query to obtain the month, the weekday, & the year from the ‘date’ fld. The primary key in this table is also, EmployeeID (from the Employees_Info_tbl).
    I sort of gave you a brief layout of the 2-tables I’m working with & I wanted to create a query displaying the number of years all of the active employees have been working for me. So from the Employees_Entries_tbl /qry I had no trouble creating a query just the active employees & the number of years they have been working for me. Except, what I really want is going back to the Employees_Info_tbl I created a query/form of all of the active employee’s information -&- on this same form I also wanted to see the number of years each active employee has been working for me. I was somewhat able to complete this task by first, creating a query/form with all of the employee’s info (from the Employees_Info_tbl) & I was also able to add the number of years all of the active employees been working for me. Note, I joined the ‘Employees_Info_Active_qry’ & the ‘Employees_Active_CountOfYrs_qry’ on the ‘EmployeeID’. Now here is my dilemma, this query or form cannot be edit & I don’t understand why. I need to terminate some employees who as been working for me the longest & to add some new employee’s info, but I wanted to do this on one form. BTW, when I joined these 2-queries on the ‘EmployeeID’ I tried all 3-different ways in joining these 2-queries & they are all non-edit. I’m not sure if I made any sense here, I tried explaining the best I could.

    Any advice/suggestions would be greatly appreciated! Thanks in advance!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,309
    That's a lot to digest and you may have more than one concurrent issue. However, this might help to determine why your query/form is not updatable
    http://allenbrowne.com/ser-61.html

    It might help to see a pic of your relationships. Also I'd recommend not using yes/no fields to determine active/inactive. Rather, use date fields (HireDate, InactiveDate). Any inactive employee can be filtered by the fact that if the inactive date is null, they are active. The upside is that you also get an "when" out of that. If you want an inactive reason (terminated, retired, resigned, etc.) that could be the foreign key from the reason table.
    To get the number of years anyone has worked for you could be as simple as using DateDiff function. However, such functions often render a query not updatable, in which case you don't base the form on such a query, you use a calculated control to return that value. That may be your main problem, and you shouldn't store that calculation anyway, thus you'd remove it from a query that is the basis of any form.
    Last edited by Micron; 01-09-2021 at 08:30 PM. Reason: clarification
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,221
    I think the biggest problem is that you linked the two tables in a 1-to-1 relationship.
    Quote Originally Posted by djclinton15 View Post
    The first table Employees_Info_tbl ........Each employee is given a unique ID (an abbreviation of their first & last name with the first letter of their middle name. i.e. Kim L Summers = KmLSmmrs). The EmployeeID, KmLSmmrs is set as the primary key for this table.
    I would use an Autonumber type field as the PK field, NOT a Text field.
    See Microsoft Access Tables: Primary Key Tips and Techniques


    Quote Originally Posted by djclinton15 View Post
    The second table, Employees_Entries_tbl ...The primary key in this table is also, EmployeeID (from the Employees_Info_tbl)
    Quote Originally Posted by djclinton15 View Post
    Note, I joined the Employees_Info_Active_qry’ & the ‘Employees_Active_CountOfYrs_qry’ on the ‘EmployeeID
    The PK of the table Employees_Info_tbl .should be linked to an FK field in the table Employees_Entries_tbl ..... NOT to the PK field in the table Employees_Entries_tbl
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    djclinton15 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    95
    Hi Steve. Thanks for replying to my post. Everything you said makes sense & I sort of understand. Honestly, I sometimes feel like I ought to be posting here because it reminds me of little I understand about database designing. Regardless in how much I read & watch countless videos (I'm a visual learner) on designing a database there are many key concepts which I just cannot comprehend. It's very frustrating because I do believe that I have a learning disability aside from also having a physical disability. All throughout school I got by with getting B's & C's, but I couldn't heck it in college. All I'm saying is that I'm not (& I know) I'm not on the same level as everyone else here. What little I do know about databases I taught myself & with tons of help from the members on this forum. I always had great trouble was unsuccessful with using PK & FK in many databases I tried building. A long time ago someone showed me how to join tables by one unique text field & that was how I manages to create a few databases. No, it may not be the, "correct" way in designing a database, may or may not be consider to be relational database, & may have many flaws.

    Sorry, I'm rambling here about my frustrations. I wish I had someone to advise me on how to built a database step-by-step from scratch. I did try playing around with what you suggested above Steve. I first made a copy of the database. Next, in the EmployeeID_Info_tbl I changed the PK to an AutoNumber fld which I then added to my -- wait now I'm confused. What do you mean by, The second table, Employees_Entries_tbl ...The primary key in this table is also, EmployeeID (from the Employees_Info_tbl)
    -- I already have the EmployeeID from the Employees_Info_tbl. The fld [#Yrs: CountOfYrs] (within the Employes_Info_CountOfYrs_qry) is from a query called, Employees_Active_CountOfYrs_qry which comes from the Employees_Entries_tbl.

    I don't know f this helps or not?



  5. #5
    djclinton15 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    95
    Hi Micron. I meant not to ignore you, but I didn’t realize someone else had replied to my post besides ssanfu. Anyways I wanted to thank you for your reply as well as make a few comments on what you suggested. 1. Actually today when I posted back to ssanfu I did (or tried) to include a pic of the 2-queries & how I have them joined. But the pic didn’t appear at the bottom of my message. I suppose I need to send the pic as an attachment, which I’ll try when I send you this message. 2) it’s funny because my active/inactive field is a yes/no field. 3) I quickly did some research on the DateDff Function (which I never used before) & I could not find any formula to calculate the number of years a given employee had worked for me based on the last date they worked. How I calculate the number of years an employee worked for me is; well let me mention this first. Whenever I have a date field within a table I almost always create a query off of that table & based on that Date field I’ll create a month, weekday, & year fields. For example, in my table, the Date field is called, “EmplDate.” So, in my query for my month field I used this formula, Mnth: Format ([EmplDate], “mmm”) – WkDy: Format ([EmplDate], “ddd”) -- Yr: Format ([EmplDate], “yyyyy”) Over time I have written/used these 3-formulas so often I practically know them by heart. I’ll calculate the number of years my active employees worked for me in a query & I’ll use the Count option to count the number of years each active employee worked for me. Creating that query work fine for me & I can use that query “#yrs” fld in one of my Employee_Info_qrys/frms. Except my issue is that Employee_Info_qry/frm becomes non-editable & has me so frustrated! My frustration is not so much about not being able to carry out the task, but more so for me it’s about not understanding why! One last detail that may or may not be significant is, in my Employee_Entry_tbl I have the EmployeeID as a Lookup field from the Employee_Info_tbl. I suppose I did that for assurances that only the EmployeeId from the Employee_Info_tbl are used in the Employee_Entry_tbl. Possibly that could be one of my issues, but I agree with what you said in that I may have more than one issue. I’ll try sending you that pic in an attachment.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,309
    At the top of the thread window (green bar if you have the default colour scheme) which may help you with file attachments - including database files IIRC.
    That may be your best bet. Pictures sometimes help, but if it involves anything that would provide text (data, code, calculation expressions) I usually don't bother with them. I'm not obstinate - I'm spreading my time between 2 other forums, woodworking and being somewhat of a caregiver so I help out where it's easiest. You will get your best answers if you provide a compacted and zipped copy of your db. If data sensitivity is an issue, you can manually update names to cartoon characters as is often suggested, or you can consider this
    https://www.accessforums.net/showthr...randomize+data
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,223
    Quote Originally Posted by djclinton15 View Post
    ...Now here is my dilemma, this query or form cannot be edit & I don’t understand why.
    https://www.datanumen.com/blogs/15-r...-access-query/

    Obviously at least one of your queries (counting years) is an aggregate one.

    As other people here adviced, use instead an unbound control in form or report, to display working year, calculate it there, and remove this part from query.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,309
    but more so for me it’s about not understanding why!
    Well, you have 2 links that give several reasons. As we've said, it is most likely due to a query that uses calculated fields. Might be worth reinforcing that stacked/nested queries (those that query a query) render the whole thing non-updatable. Best to calculate on the form as I said - and don't store these calculations either. Determining the years worked should be as simple as the difference between the hire and terminate dates:

    YearsWorked = DateDiff("m",#02/15/2000#,#05/18/2017#)/12 = 17.25 if you want the fractional portion. If not, then

    DateDiff("yyyy",#02/15/2000#,#05/18/2017#) = 17

    BTW, suggest you forego the use of lookup fields in tables as you've noted.
    http://access.mvps.org/access/lookupfields.htm
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

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

Similar Threads

  1. Multiple criteria query joining two tables
    By Carnior in forum Queries
    Replies: 15
    Last Post: 05-01-2015, 08:24 AM
  2. Problem with query and joining tables
    By jfca283 in forum Access
    Replies: 6
    Last Post: 06-10-2014, 09:02 AM
  3. Trouble Joining Two Queries
    By ianclark2992 in forum Queries
    Replies: 2
    Last Post: 06-28-2013, 08:18 AM
  4. Joining 2-Tables in a query
    By djclntn in forum Queries
    Replies: 7
    Last Post: 03-15-2012, 08:37 AM
  5. Joining 2-Tables in a Query
    By djclntn in forum Queries
    Replies: 25
    Last Post: 01-13-2012, 12:54 PM

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 - Senior Forums