Code for Append Query 2

[lars7]

Hi,
On my form I have a subform that groups together members of staff by a locationId so that I can record staff activity i.e.; annual leave, overtime, sickness. The subforms record source is a query made up of my staff table "tbltruststaff" and a table with no records "tbltemp" the SQL is:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS EmployeeName, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL
FROM tbltruststaff LEFT JOIN tbltemp ON tbltruststaff.Paynum = tbltemp.PayNum;

To send the records to the records table "tblrecords" I have an append query controlled by the on click of a command button, the SQL is:

INSERT INTO tblRecords ( LocationId, PayNum, Grade, DateId, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT forms!frmmain!list2 AS F_locationId, tbltemp.PayNum, tbltruststaff.Grade, forms!frmmain!list0 AS F_DateId, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp INNER JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum;

Can anyone help me with the 2 problems I have.

1)When I add data to the subform the append query will only add the records that have the data to the records table but I need to have a record for each staff member even if there is no data. So if there is 5 staff in a location and only 2 have activity that week I still want 5 records for that location for that weekending.

2)and how do I add some code to the append query that would show a message box if the records already exist in the records table. My knowledge of coding is still very basis so any help with either of these problems would be very appreciated.

 

[stix4t2]

Hey,
if you put the locationid where clause in the qrytemp, then it will only list the staff within the location. Also it may be better if you limit the staff records to just the location in the qrytemp first off.

The qrytest has to be more than the append query because you have to add tblrecords to test for duplicates.

 

[lars7]

I have changed the name of the "cntappend" query to "qrytemp" and the count has gone up to 64 which is the count of the staff.
The "cntAll" is still showing 7 when I'm trying to append 7 staff but still no message when I try to process records that are already there.

 

[lars7]

Hi I've changed the queries:

Qryaddrecords:

INSERT INTO tblRecords ( LocationId, PayNum, Employee, DateId, Grade, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT qrytemp.S_LocationId, qrytemp.Paynum, qrytemp.Employee, qrytemp.F_DateId, qrytemp.Grade, qrytemp.WTE, qrytemp.OT, qrytemp.EX, qrytemp.LTS, qrytemp.STS, qrytemp.AL, qrytemp.ML, qrytemp.SL, qrytemp.CL, qrytemp.UL, qrytemp.OL, qrytemp.ED
FROM qrytemp;

and Qrytemp:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum
WHERE (((tbltruststaff.S_LocationId)=[forms]![frmmain]![list2]));

could you cut and paste the SQL of the new test query from these 2 as I'm not following what you mean sorry.

 

[stix4t2]

lars,

Were getting there. The qrytemp looks good. All staff at a location with or without information in the temp table. This is the count now for cntAll.

Edit Qryaddrecords and add tblRecords to the query. Link tblrecords locationID and Dateid to show all records from qryaddaddrecords and matches from tblRecords. This is two seperate links with the arrows pointing to tblrecords. Then drag the locationid from tblrecords down to a column. If it autofills a field, remove the text and click the visible check box to hide. This is just for testing. In the criteria, type "is null". If the locationid is null, then tblRecords does not have the temp record with that locationid and dateid, that is why it is null.

If you preview qryaddrecords, it should show only the records that will be appended.

Save the qryAddrecords query, then save as qryTest. Change the query to a select query and save again. QryTest will now be a select query of only the records that can be appended.

Give it a try, or past the qryAddRecords sqlstatement so I can see if it is ok.

 

[lars7]

Hi stix,
I did all you said to do but I had a slight problem with the joins. When I had the 2 joins in the append query it was duplicating the records 7 times (7 records) and then when I made it a select query I could not view it, it had ambigous joins or something like that. So I removed the locationId join and left the DateId and it work like you said showing records that can be appended, here is the SQL:

SELECT qrytemp.S_LocationId, qrytemp.Paynum, qrytemp.Employee, qrytemp.F_DateId, qrytemp.Grade, qrytemp.WTE, qrytemp.OT, qrytemp.EX, qrytemp.LTS, qrytemp.STS, qrytemp.AL, qrytemp.ML, qrytemp.SL, qrytemp.CL, qrytemp.UL, qrytemp.OL, qrytemp.ED
FROM qrytemp LEFT JOIN tblrecords ON qrytemp.F_DateId = tblrecords.DateId
WHERE (((tblrecords.LocationId) Is Null));

I still have no message box though but some progress.

 

[stix4t2]

Lars,

Maybe we need to join the employee, dateid and locationid to match it to tblrecords. Each employee was being matched to all the others that would cause the 7 times 7. Is paynum unique to the employee, or what does it represent?

 

[lars7]

yes it is unique if they had 2 jobs they would have 2 paynumbers. Paynumber and dateid are the primary keys so that you cant have 2 dates for the same paynumber.

 

[PHV]

lars7, have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stix4t2]

So paynum and dateid are the unique identifiers between the qryTemp and tblRecords. These are the fields that need to be joined.

 

[lars7]

Yes I did it, but still the 7 count for both "cntappend" and "cntall" and no message. Thanks PHV I've been reading from the mysql link you posted, it's put in very simple language, I like it.

 

[stix4t2]

lars,

Are the counts before or after the append. After the append the cntappend should go to zero. The values have to be different to generate the message.

 

[lars7]

Hi stix,
If the DateId has not been saved before the count is
cntappend =7
cntall = 0

for that same location with 7 members of staff.

 

[lars7]

It works,

Thanks Stix you have been amazing for sticking in there with me. About 10 posts ago I changed the name of "cntall" from "qrytest" to "qrytemp" and told you that it was reading 64 (if you look back you will see) but I never changed it back, until now and I got the message box. Thanks again Stix and PHV for the code and Link too.

 

[lars7]

That shoul;d have been "cntappend" off course, I got too excited. %-)

 

[stix4t2]

Yea Lars,
It was quite and endeavor, but I think you putting a couple more tools under your belt so thats good. Give a man a fish he eats for a day, teach a man to fish, he eats for life. Good luck in your next adventure.

 

[lars7]

Yes I like Dcount, I can think of another question but I'll leave that for another night.