Form (based on table) that only shows records that are also listed on Qurery with a Max Function

[Ctrl-v]

Hi,

Any help is greatly appreciated!

A little background:

1. Table of Clients.

2. Table of Admissions (associated with each client) (there can be many admissions associated with each client).

3. Table of Discharges (associated with each client) (there can be many discharges with each client).

4. Query pulling the max date between the admissions and discharges for each client to determine which Clients are currently enrolled. I use this Query to show my current enrolled.
5. FormA: Scrolls through the Table of Clients and shows in a sub-form the associated admissions for each client.

I need a second form that will scroll through the records of clients and be able to edit the associated admissions records that are in the Table of admission, but only those admission records of the associated clients that are currently enrolled, (in other words) only those clients that show up on the Query of enrolled.

Alternatively, a button in the current FormA that will scroll through clients that are also listed in the client list of the Query, and will skip over client records who's names are not listed in the list of clients in the Query.



Thank you for your help,


ps. keep in mind I am extremely limited in my ability to code, so if you are suggested code, please try to be as specific as possible and keep in mind you are speaking with a total novice.

[Rainlover]

To start with this Form.
I assume you get the Data from ONE only table.
You retrieve this data using a Query.
Then your Form gets the data from the Query. Is this correct?
If it is correct what are you getting.
You should be able to open the query and see if this is correct. It should then be seen in the form.
Please tell me where I am wrong.
When you reply please post the SQL wording as it my be incorrect.

[davegri]

This would be a lot easier if the admissions/discharges tables were combined into one. Discharges would be indicated by a discharge date being present.

[Ctrl-v]

Thank you for your response.

I will clarify:
There is one table that lists the Clients and a bit of information about each.
There is another table that lists the admissions with more elaborate information. Each record in the admission refers to one of the clients listed in the client table. There can be admissions for each client.

My form is not pulling data from a query. It is a form that is pulling data from the client list. In the form is a sub-form that pulls all admission forms associated with each client on the list. When I scroll through the form, I scroll through each client on the list. This is regardless of whether or not the client is enrolled. I need it to be this way.

In addition to this, I also need the ability to scroll through only the enrolled clients in the form, and all the associated admissions in the sub form associated with enrolled clients will be available, so it is not pulling data from the Query.

Unrelated to this, I use the query for a report that shows all current enrolled clients.

[Ctrl-v]

Yes, you are right. It would be easier.

However, I ran into a several problem (probably due to my limitations in Access).

[davegri]

How about a main form with 2 subforms. The main form recordsource would be clients. One subform for admissions and one subform for discharges.
If It's too messy with 2 subforms, use a tab control on the main form with admissions and discharges subforms on separate tabs.

[Ctrl-v]

Okay. However, how will this help me to only scroll through those that are currently enrolled.

Currently enrolled is determined by finding the Max discharge date and Max admission date for each (as there can be multiple for each). It the admission date is the latest for a client, then he is currently enrolled. If the discharge date is most resent, then he is currently discharged.

I only want to scroll through those that are currently enrolled and not those that are currently discharged, so I can make another sub-form, but I need to be able to scroll through only those that are currently enrolled.

[Gicu]

You need to create some totals queries to show you the maxofdate for the two events (discharge and admit) by ClientID then compare them in a query to get you a status :
AdmissionStatus:iif ([MaxAdmit]>[MaxDischarge],"A","D") and finally you use that to limit your form's record source to see only the "A"s.... If you can post a stripped down db it would help...
Cheers,
Vlad

[Rainlover]

You need to create some totals queries to show you the maxofdate for the two events (discharge and admit) by ClientID then compare them in a query to get you a status :
AdmissionStatus:iif ([MaxAdmit]>[MaxDischarge],"A","D") and finally you use that to limit your form's record source to see only the "A"s.... If you can post a stripped down db it would help...
Cheers,
Vlad

Your form can get Data from a Table. If you get the data directly then you cannot filter the data.

If you get the data via a Query you can do all sorts of things. This includes sorting data. ABCDEF. Only see the current records. etc.

So what are you using?

It really would be good if you uploaded your tables queries and forms for me to look at closer.

What you have explained you are doing should be easy to do.