Complex Table Updates

[djmixer135]

Hi there,

I have a 'Master' table held in an MS Access database which looks a bit like this:

Date Added To Table	Human ID Number	Company Name	Status	Notes
01/01/2019	1	def
01/01/2019	2	def
24/08/2018	1	abc	Call	This is a note
01/01/2019	3	abc
01/01/2019	4	ghi

I also have a monthly 'Update' report containing a complete list of up-to-date records. It looks a bit like this...

Human ID Number	Company Name
1	def
2	abc
3	abc
4	abc
5	ghi

I want to add new records to my Master table from the 'Update' report using the following criteria:

• Where the combination of a 'Human ID' and 'Company Name' is unique, append the record to the 'Master' table. This means ignoring any records where the 'Human ID' and 'Company Name' combination already exist on the 'Master' table.
  
  • In the example above, Human ID number '1' would not be added to the 'Master' as the combination of '1 - def' already exists.

Now here's where it gets complicated...


Once the update report records have been appended to the Master sheet, I need to identify any duplicate 'Human IDs'. This can occur when a human changes company (like in the case of Human ID '2'). '2 - abc' would have been added to the bottom of the Master from the first step (as it has a unique combination of 'Human ID' and 'Company Name'), but '2 - def' will also​remain to exist on the Master. Assuming the old record '2 - def' has no status assigned to it and no notes assigned to it, it can be safely removed from the Master table, preserving only the newest record from the update report. However, in the event the old record contains a note and/or status, it is important that both the old record and the new record are preserved for historical purposes.

Has anyone got any ideas as to how I can automate this update in access using a query of some kind?

I'm fairly new to Access so any advice would be greatly appreciated.


Many thanks!

[orange]

Most database applications are dependent on the quality of the tables and relationships. Normalized tables are critical.
A clear description of your proposed application in simple terms is a good starting point. Designing on the fly can be a long, troublesome approach.

What do Human ID and Company Name mean in your environment?

It sounds like you have Person and Company tables and a junction table/association where one Person can be associated with 1 Company at some point in time, and possibly other Company(s) at different times.

Person --->PersonAssociatedWithCompany<---Company

See the link in my signature for articles on Database Planning and Design.
Good luck with your project.

[djmixer135]

Hi Orange,

Just to clarify, this is NOT a relational database and cannot be due to the limitations of the source data (I won't bore you with the details on this).

[orange]

Perhaps you should tell us a little more.
You are using Access or at least asked the question

...how I can automate this update in access using a query of some kind?

And yet you say it isn't a relational database.

Please provide some info to clarify exactly what you have; why it isn't a relational database; and why you want an answer to include Access.

I have used Access over the years as a " sort of editor" to pull and parse fields to assemble data for another process, so a little more info would be helpful.

Report has a specific meaning in Access.

You may be interested in this article on composite unique index.

[djmixer135]

What I mean to say is, none of the tables within my Access database are 'related'. In this example, we are only dealing with two standalone tables, an update table and a master table. These tables have no relationship set-up between them.

[orange]

Can you post a copy of the database ---don't need all your records? Only a few records will suffice and if the data is private you can mock up a few. Just ensure any made up data exhibits the same error.

[djmixer135]

I cannot do this for data protection purposes. The example above should be sufficient.

[orange]

I mocked up your set up:

I added a composite unique index on djmixMaster with fields HumanId + CompanyName

This is my djmixReport table ( i added a date field to show which month the report data represented).

HumanID	CompanyName	DteReport
1	def	11-Apr-19
2	abc	11-Apr-19
3	abc	11-Apr-19
4	abc	11-Apr-19
5	ghi	11-Apr-19

This as the query I used and the message I received.



This is the updated master

DateAdded	HumanID	CompanyName	Status	Notes
24-Aug-18	1	abc	call	This is a note
01-Jan-19	1	def
16-Apr-19	2	abc
01-Jan-19	2	def
01-Jan-19	3	abc
16-Apr-19	4	abc
01-Jan-19	4	ghi
16-Apr-19	5	ghi

[djmixer135]

Many thanks for this. That seems to be the first part of the update complete.

How would I go about the second part?