Search for all information of a particular client

[suzacaru7]

HI,

I am doing a database for my beutition shop. I created a table called slimming, which i store the clients info (including ID card number), the date of the appointment, the type of therapy the client took that particular date, and the weight of the client.

I created form so that I will enter the information from this form. For every slimming appointment I have to enter the information of every client in a separate form. Then I created a report which shows all the clients information in one report. I grouped the informmation by the ID card number so that the slimming appointments of that particular client will be grouped together.

This report can be accessed by pressing a button that will open the preview of the report. When I open the report, I have to press ctrl + F and enter the ID card number the particular client, to find all the appointments of that client.

Can you please tell me how can I do to improve my method? For example do a table or form that will display certain amount of rows to enter this information. Or else some kind of filtering in the report, to display all the information of one client.

Thanks a lot for your help


Sue

 

[Big Pat]

What I usually do create a blank form called frmSearch for instance and create a new text box on it called txtSearch or something like that.

Next I have a query, in which I return just the fileds i want (maybe from several tables), but for just the one record I need. So in your case you would have IDCardNo and in the criteria line of the query, enter [Forms]![frmSearch]![txtSearch]

Then I base a new form (frmResults for instance) on that query so I can arrange the various fields how I need them.

Finally, I go back to frmSearch and add a new command button that opens frmResults.

The outcome is that you have a box to enter your IDCardNumber and botton right there, labelled Search or OK or Go or whatever that will show you the info you want.

I hope this helps.

 

[NigelShaw]

Hi Sue,

im not the most experienced here but in my opinion, you would be better doing-

having you Client details in 1 table excluding any appointment, weight info

create a table for Client Contacts
ClientID (PK)
Name
Address1
Address2
etc

Create a table for Client Details
DetailsID(PK)
ClientID
Weight
Height
etc

create a table for appointments
AppointmentsID(PK)
ClientID
TreatmentID
TreatmentDate
etc

create a table for Treatments
TreatmentID(PK)
TreatmentDetail
etc

link these together in the relationships area.
use ClientID to link the tables together creating one to many relationships

link the Treatments table to the appointments table via TreatmentID

thus

Every Client has many appointments, many variable details
every appointment has many treatments.

your client might have say Nails & massage! ( 2 treatments, one appointment ).

from here, you could the create queries to gather your data. you might want to get all appointments for one client or check how many appointments a client keeps missing.

you could set up your forms based on the queries so the form will show all of the data you want to view.

you should try and write down exactly what you want to see, create your queries from there.

( this is only my view but someone might have a better idea )

good luck

NS

 

[suzacaru7]

Thanks a lot for your information. I tried it and worked fine. How I liked

Just to know if it is possible, Can all the records be in one page, and not every record will be in different page, and I have to press the next?

 

[NigelShaw]

Hi,

with subforms, you can have all data shown in 1 form. you link the subforms either by using the wizard or Master / Child links.

in effect you could have the form based on your client,
have subform1 based on appointments which would show all of them if the form view was continuous or datasheet view

NS

 

[suzacaru7]

Thanks I managed to do it and I am very happy after crushing my mind thinking. hehe

I don't know if you can help me with this, because now it is getting nicer hehe.

I wish to make a kind of button or check box, that when it is ticked or clicked, two free rows will be inserted automatically. This function I need to use, because if my client pays for 15 treatements at one time, the client will get 2 free sessions (treatements).

Thanks for sharing your knowledge much appreciated!

 

[themurph2000]

What I usually do create a blank form called frmSearch for instance and create a new text box on it called txtSearch or something like that.

Next I have a query, in which I return just the fileds i want (maybe from several tables), but for just the one record I need. So in your case you would have IDCardNo and in the criteria line of the query, enter [Forms]![frmSearch]![txtSearch]

Then I base a new form (frmResults for instance) on that query so I can arrange the various fields how I need them.

Finally, I go back to frmSearch and add a new command button that opens frmResults.

The outcome is that you have a box to enter your IDCardNumber and botton right there, labelled Search or OK or Go or whatever that will show you the info you want.

I hope this helps.

Well, it helped me. I needed to filter records on a form but by several factors. Just have to approach it from a different angle, I guess. Thanks.