Search code examples
sqlvbams-accessrelational-database

How to auto-populate fields in one table based on two other tables in MS Access?

I need to store some data in Access. To check how it works I have tried to make a simplified structure with three tables: Workers, Months, Reports. In the Months table I simply list months of the year to be used in Reports. In the Report table I want to store the number of items made by every worker during each month. So I have relationships: Reports.Worker -> Workers.ID, Reports.Month -> Months.ID. Please see the picture: enter image description here

Goal: Every worker will have 12 corresponding records in Reports - for each month. I want to create Reports fields like "Worker1, January", "Worker1, February" etc. so that later I can simply add the reports without manually filling Worker and Month fields.

What I've tried:

  1. In Query Design I made a query with Reports as the base table. I added joins to the other two tables and set the join properties to "Select ALL records from 'Workers'..." and "Select ALL records from 'Months'...". Then I added fields Workers.WorkerName, Months.MonthName, Reports.Report. However, this gives me error "ambiguous outer joins" and suggests to create two queries.

  2. I made a query with Reports and Workers and set the join property to "Select ALL records from 'Workers'...". So now I do get reports automatically created for every worker.

My query:

enter image description here

Result:

enter image description here

Next, I made another query. There I joined the first query with Months table. Again, I set join property to "Select ALL records from 'Months'...". However, this seems to override the data I had in the first query.

My query:

enter image description here

Result:

enter image description here

If I delete the join between the query and the table, I do get the desired result. But it is not updatable, I cannot input any reports.

Query:

enter image description here

Result:

enter image description here

I hope my explanation is clear enough. Any help is much appreciated!

Solution

  • To 'batch' create a set of month records for each worker in Reports table, use an INSERT INTO SELECT action SQL that generates a Cartesian product of the two tables (records of each table are associated with each record of other table). The structure would be:

    INSERT INTO Reports([Month], Worker) SELECT Months.ID, Workers.ID FROM Months, Workers

    Can manually execute this with Ribbon Run or build VBA or macro procedure to execute by button click on form.

    To prevent accidental duplication of Month/Worker pairs, set these two fields as a unique compound index in Reports table.

    Month is a reserved word. Advise not to use reserved words as names for anything. Better would be MonRep. Also advise not to use exact same field name in multiple tables. Rename ID fields to be unique.

    When new worker is later added to Workers via form data entry, VBA can run code to create Reports records for that new worker. First, code would save record on form then execute SQL:

    DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "INSERT INTO Reports([Month], Worker) SELECT ID, " & Me.ID & " FROM Months"