creating a form (1 Viewer)

[Terkpeh1990]

hi am new in access . am creating this system for an eye clinic and i want to be able to copy the values entered by the doctor and place it on the form assign .and once it is done and the search button is clicked, it should be able list all glasses having similar prescription in the system.

can anybody help me out with how to do that am kind of stuck and dont know what to do . am only familiar with searching using one textbox . any help would be greatly appreciated.

 

[plog]

Tables; Then reports/queries/; Finally forms.

You need to go back to step 1 and work on your tables before you move on. And when you do move on, it should be to make reports. No point making beautiful forms if you are unable to extract the data from your database like you need.

Overall, read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the process of setting up database tables. Specifically, these are the things I see:

Same data in 2 spaces---there shouldn't be both PATIENT and PATIENT_DETAILS table. Could there be 1 record in 1 of those tables and more or less than 1 record in the other? Seems you have split your data unnecessarily.

Storing redundant/calculated data --- I see you have a field for both Patient birthdate and patient age. There's no need to store age--you can caluclate it when you need it.

Poor names 1---"Date" is a reserved word in access (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe) and will make coding/querying harder done the line.

Poor names 2-- Further, "date" is horribly generic name. What does that date represent? Prefix or suffix the word date with that (e.g. AppointmentDate, ArrivalDate, etc.). Make meaningful names.

Poor names 3-- use only alphanumeric characters in table and field names (that means no spaces). it will make coding/querying easier down the line.

Improper foreign key --- How are PATIENT and PATIENT DETAILS linked? You knew to add a an autonumber ID field to PATIENTS, but you don't use it. That is the value that should be in PATIENT DETAILS to link them (of course, PATIENT DETAILS should go away, but see my point).

Wrong data types--You have a PATIENT ID field in ISSUE, however it is a Short Text field. That will not work to hold the ID from PATIENT, it must be a Long Integer. I fear you have done this a lot--I bet you have a lot of short text fields that need to be numeric.

Unnormalized--read that link I gave you, this is your big issue. You have too many fields in most of your tables. Essentially, you are storing values that should be in records in a table, in field names.

Let's take the field CURRENTSPECRX_SPH_RighEye_PAIR_A in CONSULT as an example. Without knowing much about your data I know that you are storing at least 3 pieces of data in the name (my bet is it is more). Although I don't know what SPH is, it should not be in any field name, same with 'Right Eye' and the 'A'. You need a whole new table to accomodate that data.

Read up on normalization, apply the fixes to your database, set up your Relationship Tool completely, take a screen shot and post it back here so we can go over your progress.

 

[jdraw]

plog has given you good advice.
In addition to what has been offered, here is a link to several videos, articles and tutorials on Database Planning and Design.
Make sure you do your analysis and design before getting too deep into physical database.
Good luck.

 

[Terkpeh1990]

Tables; Then reports/queries/; Finally forms.

You need to go back to step 1 and work on your tables before you move on. And when you do move on, it should be to make reports. No point making beautiful forms if you are unable to extract the data from your database like you need.

Overall, read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the process of setting up database tables. Specifically, these are the things I see:

Same data in 2 spaces---there shouldn't be both PATIENT and PATIENT_DETAILS table. Could there be 1 record in 1 of those tables and more or less than 1 record in the other? Seems you have split your data unnecessarily.

Storing redundant/calculated data --- I see you have a field for both Patient birthdate and patient age. There's no need to store age--you can caluclate it when you need it.

Poor names 1---"Date" is a reserved word in access (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe) and will make coding/querying harder done the line.

Poor names 2-- Further, "date" is horribly generic name. What does that date represent? Prefix or suffix the word date with that (e.g. AppointmentDate, ArrivalDate, etc.). Make meaningful names.

Poor names 3-- use only alphanumeric characters in table and field names (that means no spaces). it will make coding/querying easier down the line.

Improper foreign key --- How are PATIENT and PATIENT DETAILS linked? You knew to add a an autonumber ID field to PATIENTS, but you don't use it. That is the value that should be in PATIENT DETAILS to link them (of course, PATIENT DETAILS should go away, but see my point).

Wrong data types--You have a PATIENT ID field in ISSUE, however it is a Short Text field. That will not work to hold the ID from PATIENT, it must be a Long Integer. I fear you have done this a lot--I bet you have a lot of short text fields that need to be numeric.

Unnormalized--read that link I gave you, this is your big issue. You have too many fields in most of your tables. Essentially, you are storing values that should be in records in a table, in field names.

Let's take the field CURRENTSPECRX_SPH_RighEye_PAIR_A in CONSULT as an example. Without knowing much about your data I know that you are storing at least 3 pieces of data in the name (my bet is it is more). Although I don't know what SPH is, it should not be in any field name, same with 'Right Eye' and the 'A'. You need a whole new table to accomodate that data.

Read up on normalization, apply the fixes to your database, set up your Relationship Tool completely, take a screen shot and post it back here so we can go over your progress.

some of the forms and tables were not suppose to be there . i forgot to delete them before uploading .

the patient details and patient form both take their record source from consult query .

when and information is entered into the the patient details form, and the patient gets to the doctor , the doctor then type the id given to the patient by the secretary into the patient form to retrieve the personal details of the patient and then continue from there

and as for the naming in the tables like CURRENTSPECRX_SPH_RighEye_PAIR_A. every text box is linked to a field . and the fields are plenty . so for me to not get confuses as to which filed is going to which text box, i named the fields according to the labels on the forms.

once the information the dotor needs to input is done, and the doctor clicks assign glasses, the values of the FINAL FX is copied and when the assign form opens, the vaules copied is populated into thie corresponding fields. the moment the search button is clicked , it should pull out similar glasses with similar prescription values in the product details .so that the nearer one can be selected. the issue is i dont know how to go about it . is a system which will be used to run a charity which distribute free prescribed glasses to the children whose parent cant afford it . need help

please help me so this system can start running. be a part of a charity work .thank u

please any help would be greatly appreciated. find attached a copy of the work done so far

 

[plog]

some of the forms...patient details and patient form... entered into the the patient details form...into the patient form...according to the labels on the forms....when the assign form opens...

Sounds like you have chosen the tough love approach to us helping you. I prefer that one. So let's get started.

The process for building a database isn't dependent upon the type of organization it is for. Kind of like how the tables aren't dependent on how the forms are to look. Charity, job, school assigment, hobby project, who cares--you always start with a noarmalized table structure. I reiterate, (unfortunately I did not misuse that word, this is the third time I've said this): read up on normalization (https://en.wikipedia.org/wiki/Database_normalization).

Structure your tables properly. If you really want to help this organization then build this database correctly, otherwise you are building an unusable heap that only you will be able to get to halfway work.