One to Many subform to appear as a single datasheet

[amy3000]

Table 1 consists of basic client data and the Client ID is the primary key. Table 2 is entry and exit data per admission to the program by client. Each client can have multiple admissions to the agency.

I want a datasheet subform with all the basic client data plus all admissions for that client. I would like it to be visible always in the Client Data Form. This would be for the data entry person to refer to. I created a query that shows it the way I want it. If Mary Smith has two admissions, in the query her name appears twice with each admission date, like the example below.

ClientID Name AdmissionID AdmissionDate
1 Mary Smith 1 1/15/01
1 Mary Smith 58 7/2/02

When I try to create a form, I can't seem to make it appear as a single datasheet. It creates a form with the client information and then puts in a subform with the admission info. I just want a listin a datasheet. Is there a way to make it do this?

Thanks as always. I've had help on 3 different databases in the last few days. Everything helped. I've learned an enormous amount from this forum.

 

[zambezibill]

If I have understood your question correctly, create a SELECT query joining table1 and table2 (include the fields containing the client name) and use this query as the subform's recordsource.

Cheers, Bill

 

[thornmastr]

I set up tables based on your post, in fact the same fields that you used with one addition. I included a forward key in the admissions table which mapped back to the client id of the client table. I then defined a relationship between the Client Id of the client table and the forward key on the admiissionsd table. I then built the form which is just what you wanted in datasheet view and had no problem. You should have no problem using it as a subform.

If you will tell me the version of Access you are using and give me your email address, I will send you a zip file. I think it is what you are trying to do.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[amy3000]

It wasn't what I thought it was at all. It was the darned report wizard. I had done the type of query that Bill suggested. Robert did exactly the same thing. The problem was that I didn't realize that you could design a form without the wizard and have it appear in datasheet form. Ahh, the little things. I figured that out because Robert was kind enough to work this through and e-mail me a query and form. Thanks again, Amy