Create 2 additional fields to a table using a query

[MGordinho]

Hello!



I have 2 tables:


Table A has the allocation history of some events
Table B has events not finished


I need to create a new table that has all the events in table B and 2 new fields:


1. Field 1: Checks if the event is already in table A
2. Field 2: Checks if the event status has changed


I have no idea how it is done. Can somebody help me?


Thanks

[JoeM]

You can use a Make Table Query to create your new table.

I don't think you need Field 1 in your query at all. The General Rule of Thumb is that you should NEVER store anything which can be easily calculated "on-the-fly" at any time. You can use a simple query to see which records in your new table also have records in table A.

Regarding your second field, you really have not mentioned anything above it about a Status field, and how you determine if an event has changed. If it is a matter of looking at other records in other tables, it will probably fall into the same situation as above. In which case, you may not need a new table at all, but rather just some simple queries between the two tables that you already have.

[MGordinho]

I'll try to explain exactly what I want to do.


As I said I have 2 tables:


A: History of events allocated
B: List of events not finished


Table A has several columns: Event ID | Status | Error Description | Employee | Allocation Date | Solved? | Solved Date


Table B has the following columns: Event ID | Status | Error Description


I want to check if the events in table B hava already been allocated to someone. The criteria must be:
- if the event is not in table A, or
- if the event is in table A but the status has changed


If these conditions are not met, I need to allocate those events. How do I get a list of those events?

[JoeM]

Can you post some a small data example, showing your different situations, and what you would like to see returned?

[MGordinho]

I hope this helps...

[JoeM]

It looks to me that you just want an Unmatched Query between Table B and Table A, joining on both the Event ID and Status fields (basically, it looks like you want all records from Table B where the Event ID/Status field combination is not found in Table A).

The SQL code for that query would look like:

SELECT [Table B].[Event ID], [Table B].Status, [Table B].[Error Description]
FROM [Table B] LEFT JOIN [Table A] ON ([Table B].Status = [Table A].Status) AND ([Table B].[Event ID] = [Table A].[Event ID])
WHERE ((([Table A].[Event ID]) Is Null));

[rpeare]

may want to throw in a solved date being null as well since it looks like he wants to exclude solved items.

[MGordinho]

It works just as I wanted. Thank you.


However, I need one more criteria in my query. An event can have a status, evolve to another and get back to the previous one. In theses cases, I will need to allocate them again.
Is it possible to check what was the last status on Table A? And if the last status in Table A is different from the one in Table B, the event should be in the query result, in order to have the complete list of events that have to be allocated.
The events in Table A are sorted by date (oldest to newest).

[JoeM]

Is it possible to check what was the last status on Table A?

Take a look at creating an Aggregate (Totals) Query to return the Maximum value in your date field for each Grouping.

See:
https://www.techonthenet.com/access/..._query2007.php
and
https://access-programmers.co.uk/for...d.php?t=159665

(the second example shows you how to add levels for Grouping and not just return one single date, but a date for each group.

[MGordinho]

Thanks! Just what I needed

[JoeM]

You are welcome!