Conditional query pulling data from two tables

[BonnieG]

It's me again, being annoying...

So I have two tables:

users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...

user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...

In user_change I record any changes made to the users table.

What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.

I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.

This is the data I want to pull from user_change...

The MOST RECENT action_date WHERE change_type is LIKE "*issued*".

However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.

At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...

I don't even know how to explain myself... I wish I was more knowledgable.

If anyone can offer some advice, as always I would be ever grateful...

 

[plog]

This will take a sub query. First build a query using just user_change to identify the last issue date. In design view, bring in user_change. Then bring down the fields user_id, action_date and change_type. Change the query to an aggregate query (click the Sigma/Summation sign in the ribbon). Then underneath action_date, change the 'Group By' to say 'Max', and underneath change_type change it to 'Where' and put your criteria beneath it.

Save that query as 'sub1'. Then open a new query in design view. Bring in users and sub', link them by user_id. Then change the linking to show all from users and just those in sub1 that match. This is a LEFT JOIN and will show all records in users regardless if they are in sub1 or not. Next, bring down all the fields from either table you want and run it.

 

[BonnieG]

Thank you SO MUCH for the very detailed and foolproof instructions (you may be able to guess I need a bit of detail for things to make sense as I am still learning - slowly)! That worked like a charm. Thanks sooooooo much!!!

 

[BonnieG]

Hmm... I've just double checked the output and I'm still getting duplicate rows from "users" for those users who have two or more entries in "user_change" which match my criteria... not sure how to resolve this one?

My subquery:

SELECT dbo_ipms_user_change.ipms_user_id, 
Max(dbo_ipms_user_change.action_date) AS [Issue Recorded Date],
dbo_ipms_user_change.actioned_by AS [Issue Recorded By],
dbo_ipms_user_change.user_change_summary AS [Issue Notes],
dbo_ipms_user_change.username
FROM dbo_ipms_user_change
WHERE (((dbo_ipms_user_change.change_type) Like "*issued*"))
GROUP BY dbo_ipms_user_change.ipms_user_id,
dbo_ipms_user_change.actioned_by,
dbo_ipms_user_change.user_change_summary,
dbo_ipms_user_change.username;

Any ideas? Sorry, thought it was sorted but not quite!

 

[plog]

That query is using user_change, so I assume its the sub1 query I was talking about. The reason you have 'duplicates' is because you have all those additional fields. Why are they there?

 

[BonnieG]

That is the subquery yes. I didn't think adding additional fields would matter? (The reason I didn't mention those additional fields is because I don't need to do anything "different" with them - just place the values into my query results.) I'm struggling to see why having those fields would cause duplicates when the criteria (e.g. WHERE) is only on one field? Is it because it's a special type of query? I will admit I haven't used this type of query before.

 

[plog]

I put 'duplicates' in quotes in my last post because they are only duplicates to you. If you run the query you posted you will see every record returned is unique.

Sure, some user_ids appear multiple times, but that doesn't mean the row it appears in is a duplicate of another row. By bringing in all that other data, you allowed multiple user_ids to appear in your query.

 

[BonnieG]

Thanks for the explanation. I'm still a bit confused but it's not a big deal as I can live without the other data in there. Many thanks for all your help.