How to Populate data in a table from the Newest Record in a SubTable

[SealM]

Hello all,


I'm a very new Access user, and this is my first database project. It's supposedly a simple database designed to track the checking in and out of Aircards (cellular cards for laptops). I'm not very good at explaining what I want to do, and I've searched for this solution, but I haven't come up with anything that clicks in my brain - but here goes. =)

1. On my main form (Add Aircard Form) and table (aircard_master) there is a value for Current Assigned User (aircard_master.userid)
2. On the main form, there is also a subform that shows actions associated with the currently selected aircard on the main form. (actions such as when it was issued, returned, activated, assigned a phone number, etc) and each action is required to have a date assigned to it. These actions are stored in the table (aircard_actions)

My proposed problem is that I want the Current assigned user to be auto-populated with the last user (aircard_actions.user) when data is entered into the subform (aircard_actions). All this can be seen/done on the main form. At this point, I'm unsure whether it needs to be done in a query, as code when a new action is recorded in that table, or what. But, the data needs to update the original table (aircard_master.userid) automatically based on the latest DATED entry for that associated card (aircard_master.inventory_number), from a non-null entry in the (aircard_actions.user) field.

This was confusing to me even as I wrote it, I know the people here are a lot smarter than I am on these matters, so maybe in makes sense to you. I'm going to try to find a way to link an attachment of the database with test data in it if anyone wants to take a look. It's 2.5MB so I can't attatch it here...
Oops, just figured out how to share it online. Here's the download link for it:
http://temp-share.com/show/KdPfsL3ih

Thanks in advance if anyone has any ideas!
Mike

[ssanfu]

Hi and welcome to the forum

What does your table structure look like? Right now I can see 4 tables:
a table for Aircards
a table for Users
a table for Actions
and a junction table AircardUser

The junction table would have a FK from Aircards, a FK from Users and a FK from Actions, plus the other fields you need (ActionDate, etc)

Maybe you could post a jpg of the current table structure....

[SealM]

Sure thing. I don't know what an FK is. Here are photos of the main form. In the middle of the form is the box for assigned user, that I want auto-updated based upon who currently has the card checked out to them. That information would be derived from the last CHECKED OUT/IN in the subform at the bottom of the page. So in this instance, the current record would be updated with the user: jbaker, since he was the last person the aircard was checked out to on the subform below.
Also, what happens when another field is added and CHECKED IN is applied? Is there another piece of code or action that has to be associated within the same action set/code/query?

[ssanfu]

You should read up on normalization. Crystal has a good site to begin with:
http://www.accessmvp.com/Strive4Peace/

Search the forum for other sites.

You need to have a good table structure to begin with. Right now you don't have a normalized structure, but it is close. It sounds like your main focus is to track air cards so you need to modify the relationships.
"FK" stands for Foreign Key, the field that is linked to a "PK" (primary key field). Each table should have a PK; there can be zero to many FKs in a table.

Once the structure is correct, then the forms/reports will be easier to create.

I will be able to look at your dB tonight.....

[SealM]

Thanks Steve!
I've been reading Crystal's guide, and it is very enlightening. I've gone back and made some fundamental changes, and I may just end up scrapping everything and starting over. I really want to nail this query down, however, and I think I'm close, but I'm not there yet. Let me try to explain in a different way, and show my tables. I think this is basically an SQL problem in finding this value, and in all the queries I have tried to write, they come back with multiple rows of data and not just the LAST date. Here's what I have:
The following table stores all information about aircards (aircard_master):


And here's the next table, which lists all actions associated with the aircards (aircard_actions). The (aircard_actions.aircard_id) column is a FK associated with the (aircard_master.inventory_number) column from the above table. You can see on the above picture, that each (inventory_number) will expand to show all associated records matching it in the below table.


And finally, I have to add this table for reference... the (aircard_action_list) table is just a list of actions used in the drop-down selection for the above table in the (aircard_actions.action) column. This is important for the query.


Now, for the query problem... What I want to do is this:
Find the user from the subtable located at (aircard_actions.user) for a given inventory number (aircard_master.inventory_number) where the aircard action (aircard_actions.action) is either "check out to" or "check back in" (and this is looked up from the table on the 3rd picture above, translating to [2 or 3]) that has the MOST RECENT date, which is found in the (aircard_master.action_date) column.

So basically, I want a query to return the user (or lack therof) of an aircard based on the most recent entry in the (aircard_actions) table, which can be found only by looking at the records in this table that match 2 or 3 in the (aircard_actions_list) table, which would be "check back in", or "check out to". I wouldn't want to know the user entry if the most recent entry was that the card was "damaged", since that data would be useless, for example.

Here's what the query I've gotten the closest looks like:
SELECT aircard_master.inventory_number, Max(aircard_actions.action_date) AS MaxOfaction_date, aircard_actions.User, aircard_actions.action
FROM aircard_master INNER JOIN aircard_actions ON aircard_master.inventory_number = aircard_actions.aircard_id
GROUP BY aircard_master.inventory_number, aircard_actions.User, aircard_actions.action
HAVING (((aircard_actions.action)=2 Or (aircard_actions.action)=3))
ORDER BY aircard_master.inventory_number DESC , Max(aircard_actions.action_date) DESC;

But it returns multiple values for each card (1 for each user by the latest date checked out, and the latest checked in.) I would only want to know 1 result for each card.


Anyway, let me know what you think. I will keep reading in the mean-time! Thanks again.

[ssanfu]

Keep reading about normalization.......



Your structure is still not right. In the table "aircard_Master", how is "actions" an attribute of an aircard? "inventory_number" - OK. "phone_number" - OK. "au", "esn", "comments" - OK. "Actions"??? Nope - goes in another table. (there will be many actions)

I modified your database. Tear it apart and see if you understand what I did. Look at the relationship window. Post back with questions.....


Also, read about the "EVILS OF LOOK UP FIELDS" http://access.mvps.org/access/lookupfields.htm

Look up TABLES are a different matter. They are good.

[SealM]

BRILLIANT!!
I love what you did, it explained so many things!! You are definitely THE MAN, at least as far as I am concerned. Everything is much more fluid, and I see how the use of PKs and FKs are so important to lay out in the beginning. Re-making the tables and relationships was something I knew I would need to do, but I did not realize the ease in which it could be done, adding FKs to basically ANY field that would be repeated in a table.

All kudos being said, I've worked on cleaning this up and making it pretty, but I have one more issue. Before I ask of you a final question, let me lay the ground-work on this database:
It's to be used just to track these aircaards, as I'm sure you are aware. It will be used by many users, most of whom do not have any knowledge or even business using Access. That notwithstanding, the project must still take place, so I have made supreme efforts in creating this so that data can be collected, and secured as much as possible from deletion, yet very easy to use.

So, what I've done (and did in the last database which you removed, which was fine), is make a pop-up form for adding data to the aircard_actions table. This form is represented by the "add an action to this card" button on the main form page. When I hit this button, I want the popup form to come up and enable a user to enter an action associated with this card. I've gotten most of the form to work, with two exceptions:
1. the "state inventory control number:" field is not working properly. I wanted this field to pop up with the value with the same name that was on the previous form.
2. That same field, when selected (and it can even be invisible), does not change the actual value from the table it will update (aircardID_FK). I've tried to do THIS for the entire day today, but to no avail. Just being able to select the card name (State Inventroy control number) would be a huge win in my book, but every time I select something, it wants to add a NEW record to the "AircardID_FK) field instead of populating it with a previous record (which would be from the [aircards.state invntory control number] field. It makes it more complex for me, because this field is unique, and is actually not the key, the key in this table is [aircards.aircards_PK].

I know all this is probably confusing, so I've attached the database as I have it now so you can take a look at it. I have a feeling this is an easy fix, relating to the lookup data query in the "inventory_number" combo box on the aircard_popup form. I just am at a loss for how to get this combo box to update the aircard_actions table properly and to show an accurate list of the aircards.

Thanks again for all your help. I know that was probably a lot of work, but if you can direct me just a bit more, I will leave you alone and owe you forever!

Here's the updated database:
http://temp-share.com/show/YgFHcB2Gy

[ssanfu]

OK, I added the pop-up and locked the controls on the sub form.

You really should stop using spaces in object names. They are a PITA.

Hope this is what you want....

[SealM]

That's it! I've done it! and by that, I mean *you've* done it. hehe. Thank you so much for the brilliant work. Now I'm off to populate data and start creating reports. Cheers to you. =)