Auto Populate records in table

[oxicottin]

Hello, I have a table (tbluBoughtPTO) that I want to auto add a record for each active (tbluEmployees.isInactive = False) employee (tbluEmployees.EmployeeID)
and have the button on my form (frm_AddEditAccuredPTO) and on the form there is a textbox (txtYear) which I would enter a year that would be saved for each new auto record in the field (tbluBoughtPTO.BoughtYear)

How can I do this? I don't even know where to begin.... Right now, I have to manually do this for each employee each year.

Thanks,

 

[plog]

Sounds like a simple INSERT query:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

First, make a backup of your data/database. Second, build a SELECT query based on tbluEmployees and have it generate just the records/data you want. Once you have that, go to the Ribbon and turn it into an INSERT query and set tbluBoughtPTO as the destination, then in the query map the data to the fields you want to put the data in and run it.

 

[oxicottin]

@plog I created the query like you said and changed it to an Append query then added the criteria pointing to the forms text box and added inactive false to not add records for employees that arnt there BUT it not doing anything when I run it other than showing a test record, I added for the year 2024. I need it to create a record for every employee that is in (tbluEmployees.EmployeeID) with the year I enter in [Forms]![frm_AddEditAccuredPTO]![txtNewYear]

What am I doing wrong?

INSERT INTO tbluBoughtPTO ( EmployeeID, BoughtYear )
SELECT tbluBoughtPTO.EmployeeID, tbluBoughtPTO.BoughtYear
FROM tbluEmployees INNER JOIN tbluBoughtPTO ON tbluEmployees.EmployeeID = tbluBoughtPTO.EmployeeID
WHERE (((tbluBoughtPTO.BoughtYear)=[Forms]![frm_AddEditAccuredPTO]![txtNewYear]) AND ((tbluEmployees.IsInactive)=False))
GROUP BY tbluBoughtPTO.EmployeeID, tbluBoughtPTO.BoughtYear;

 

[Gasman]

Start with just the select and see what is returned.

 

[plog]

I created the query like you said and changed it to an Append query then added the criteria pointing to the forms text box and added inactive false to not add

That's not what I said. I said get the SELECT working to show just the records/data you want, then change it to an APPEND. You started with the APPEND and then tried to back into the data.

Additionally, why is tbluBoughtPTO used in your query? You should be able to create a SELECT query to display all the data you want to put into tbluBoughtPTO without using tbluBoughtPTO at all. Per your description it should simply be appended to and not used any in other manner.

 

[bastanu]

In the first column of your query change the table name to tblEmployee (and you don't need the tbluBoughtPTO table in there). I suggest you do it in two steps; leave the current query as a Select one then create a new one in which you join this new query to the tbluBoughtPTO using outer joins between employeeIDs and the year fields in each and adding criteria to the tbluBoughtPTO side Is Null (create a unmatched query) to avoid adding the recordds multiple times.

Cheers,

 

[plog]

I did try a select first and it gave me no data because I need to have data entered not pulled

If that's true then no query can help you get to where you need---yet you proceeded to try and use a query to obtain the data you needed.

In your mind you have half a solution and my full solution doesn't jive with your half solution so you don't fully hear or understand my full solution. You try and shoe horn in the parts of my solution that fit with your half solution. You need to clear your mind of your solution and try and fully understand mine.

First, every action query can exist as a SELECT query. That's always step 1. You get a SELECT query to display the data you want to perform an action with to confirm its correct. Once the SELECT is correct you can easily switch it to an action query to take the action you want.

a record for each active employee ... I would enter a year that would be saved for each new auto record in the field

Let's start simple--make that SELECT query. Stop thinking ahead, stop thinking about what already exists in tbluBoughtPTO. Make a query to do just the above. You only need one table in that query--tbluEmployees.

 

[bastanu]

I agree that the first step should be a SELECT from tblEmployee but at one point (either here on in the subsequent INSERT) you would need to bring in the tbluBoughtPTO with left outer joins pointing towards it and Is Null in the EmployeeID from it (and also criteria for the year) to eliminate the possibility of creating duplicates for the employee and year.

Cheers,

 

[bastanu]

Actually even easier would be to add a composite unique index on EmployeeID and BoughtYear fields in the tbluBoughtPTO table so the unmatched query would not be needed.

 

[oxicottin]

@plog I get it now.... I read your initial post over and over and over a few times and sorry I jumped the gun or just didn't get it, and I get it now and it works. I thought you had to have the table in there as well and linked to EmployeeID and I didnt know that when you change it to Append and select the table you want the data to go to then you can add a expression to bring in the year to the BoughtYear field.

INSERT INTO tbluBoughtPTO ( EmployeeID, BoughtYear )
SELECT tbluEmployees.EmployeeID, [Forms]![frm_AddEditAccuredPTO]![txtNewYear] AS InsertWhatYear
FROM tbluEmployees
WHERE (((tbluEmployees.IsInactive)=False));

Now I have the issue of what @bastanu is talking about. If I entered years for employees lets say 2023 and ran it again then I would have duplicate "BoughtYear" data in my tbluBoughtPTO table for the same employees. How do I prevent that?

I attached an image of what I mean....

 

[ebs17]

PARAMETERS
   pNewYear INT
;
INSERT INTO
   tbluBoughtPTO(
      EmployeeID,
      BoughtYear
   )
SELECT
   S.EmployeeID,
   S.InsertWhatYear
FROM
   (
      SELECT
         EmployeeID,
         pNewYear AS InsertWhatYear
      FROM
         tbluEmployees
      WHERE
         IsInactive = False
   ) AS S
      LEFT JOIN tbluBoughtPTO AS T
      ON S.EmployeeID = T.EmployeeID
         AND
      S.InsertWhatYear = T.BoughtYear
WHERE
   T.EmployeeID IS NULL

 

[oxicottin]

I followed this video tutorial for a Prevent Duplicate data using a composite key in the table between the year and employeeId and that solved having duplicate data.

Composite Video

Thanks,

 

[Pat Hartman]

Your problem is being caused by adding "empty" rows when you should not. For starters, what is the point of having a row in a table with only a checkbox and a year that indicates something (vacation I assume) was purchased that year? Don't you have a transaction somewhere with details about that? The record doesn't even indicate how much vacation was purchased. Where is that information stored? Why can the presence of a non-zero or non-null value in that field serve the purpose of these "empty" rows?