Append Query - new records in table A to add to Table B?

[djspod]

Hi,

I have a table of Agency drivers, new drivers are getting added daily.

Need Access to query the names in the Agency Drivers table, identify the new names and add to the Agency Week Rota.

Tried various combinations to achieve this, One query insisted on adding every record again, I only want to add the new drivers, in my case example, 2 records, ID 15 and 16.

Managed to use an unmatched query to identify the new records, created a new append query using the Agency Week Rota and Unmatched query.



Attempts to update 2 records but complains of key violation, field locked etc. etc.

I think I'm on the right path now but stuck on why Access won't allow the table to add new AgencyDriverId's?

Can anyone help please?

Driver Resource Rebuild 12.12.2017.zip

[June7]

Since the AgencyDriversID_FK field is defined as Indexed Yes No Duplicates, these two tables have a 1-to-1 relationship - so why not combined as 1 table?

Multiple similar name fields is an indicator of non-normalized structure. What are the ADx fields representing?

[djspod]

Hi June7,

Thanks for the reply.

The AD1,AD2 etc . is short for agency day 1 , day 2 etc. (Sun, Monday, Tuesday)

I thought I had the right relationship setup but may have screwed up.

1 Sunday Commencing week can have all the drivers working or a few of them (1 to many)

There will be many Sun commencing dates in the Agency week table (52 Weeks) (1 to many)

[June7]

Then change Indexed property of the field.

Then possibly set AgencyDriverID_FK and SunID_FK as compound index to prevent duplicate pairs.

SunID_FK is a required field. Your append query does not provide a value for this field.

[djspod]

Many thanks for your help, I seem to have lost the plot now.

Made both items a primary key in the agency week table, reworked several versions of the append query, the query detected 3 new records but would not allow to append due to several violations even though I had the SunID_FK in the query?
one rework included not 3 new records but 17 of each new record, i'm losing my train of thought somewhere here.

[Micron]

2 things you can do when it comes to adding a batch of records where some violate indexes.

- if a table field is set to no dupes, then when running an append query, turn off warnings, run query, and turn back on again. You won't see the warnings and any records that can't be appended are simply skipped. Those that can be are.
- often preferred is to use the Execute method of the CurrentDb object. If you use the dbFailOnError option, you can trap error 3022 in an error handling routine and simply resume next, or if that's not appropriate, don't use the option and hope what needs to be added is. Here's an example of this, including examples of concatenation with variables (n) and error handling routine.

Sub TestDbExecuteOnIndexedField()
Dim n As Integer

On Error GoTo errHandler
For n = 5 To 7
   CurrentDb.Execute "Insert into [tblYourTableName](YourFieldName) Values (" & n & ")", dbFailOnError
Next n

exitHere:
Exit Sub

errHandler:
If Err.Number = 3022 Then
   Resume Next
Else
   MsgBox "Error " & Err.Number & "; " & Err.Description
   Resume exitHere
End If
End Sub

Note: .Execute will accept a stored query rather than a sql statement as the sql argument.
P.S. Since you've 'lost your train of thought', I opted to respond to the issue of the warnings you're getting rather than try to get on board a train that has already left.

[ssanfu]

So about the image "Append Query append 19 rows.jpg" in Post #5 (bottom image).....

In "QueryAppendTest", how are you telling the query to just add the three new drivers?
And how are you telling the query which Sunday to add to the table?
The way the query is now, it looks like it will add the first Sunday (and only the first Sunday) and all drivers.
Do you want to add entries to "tblAgencyWeekRota" for the new drivers for every Sunday through 10/25/2020?


In addition, I would suggest taking a new look at your tables. It seems you have multiple tables for drivers and Rota. Very confusing.........


Why aren't the "tblEmployedDrivers" and the "tblAgencyDrivers" in the same table?
Why aren't the "tblTrailers" and the "tblVehicles" in the same table?

Why "tblAgencyWeekRota" and "tblRota"?
It looks like there are 4 tables for start times???

[June7]

Well, populating SunID_FK field with itself doesn't make sense. You need to specify some other source for this value. Either manually enter a static value or use an input popup or reference an input control on form. The following worked:

INSERT INTO tblAgencyWeekRota ( AgencyDriverID_FK, SunID_FK )
SELECT tblAgencyDrivers.AgencyDriverID, 1 AS Expr1
FROM tblAgencyDrivers LEFT JOIN tblAgencyWeekRota ON tblAgencyDrivers.AgencyDriverID = tblAgencyWeekRota.AgencyDriverID_FK
WHERE (((tblAgencyWeekRota.AgencyDriverID_FK) Is Null));

[djspod]

Hi Guys,

Thanks for your thoughts/Feedback and input.

Much appreciated!

It looks like I have made a hash of the Table Relationships, I consider myself better at this than a few years ago but obviously more understanding and work is still needed.

I'm basically aiming at building a DB like the Excel Spreadsheet below.

A form with Employed drivers displayed, per week, shift pattern etc.

This form is built and complete, works fine for any Sunday W/C selected.

Now its time to generate a Agency Driver blank sub form to go underneath the employed drivers form.

The idea being, If drivers are on holiday, I cover the driver via agency cover for the time they where starting.

Later in the week a new driver may appear after the Agency form has generated for that W/C, Hence the append query reason to append new drivers into that week or another in the future.


Once the form and sub for are working how I want them to too, I plan on generating a Report print off of the same for the wall in the office to assist the managers with planning, checking the agency etc.

Hope that makes more sense, I'll tidy the tables up etc.

June7: The SQL code you posted works great, thanks for creating it.

Just need to work hard now and tidy up the DB into some normalised state, Somtimes get confused with: Is it a Many to Many relationship and needs a JCT or is it a straight forward One to One.

Thanks again for your help.

[djspod]

[QUOTE=djspod;381156]Hi Guys,

Thanks for your thoughts/Feedback and input.

Much appreciated!


I have a screenshot to attach but I keep getting this error message when trying to upload an attachment

[djspod]

Screenshot of the Excel Driver Resource Database

https://drive.google.com/file/d/1420...ew?usp=sharing

I was unable to upload the file (Error upload file?)

[June7]

That link asks for a password.

Did you try upload with the Insert Image tool?

[djspod]

Hi June7

Tried again on another PC and for some reason the uploader now worked?

I'm aiming to achieve the Excel format below in Access.

As an Agency driver is issued and booked on a time slot, the Green Times on the Right disappear.

Sunday is a good example to look at (See Screenshot)

No shift have been covered by the Agency for Sunday, they are still in the boxes in Green on the bottom right of the report page print off.

They stay in that box until booked and entered in the Sunday column of the agency.

Hope that makes sense.

Thanks