still confused, can someone help

[plog]

So how do i add this second test to the same baby record?

You would create a new record in the Testing table. If you needed a third--a new record in the Testing table. A fourth? A new record in the Testing table....A 237th? A New record in the testing table.

As long as they have the same BabyID value, they all go to that baby.

 

[val_87]

You would create a new record in the Testing table. If you needed a third--a new record in the Testing table. A fourth? A new record in the Testing table....A 237th? A New record in the testing table.

As long as they have the same BabyID value, they all go to that baby.

Thank you! That makes a lot of sense, i can't beleive i didn't think of it before.... i need to learn to think differently, i will do up a new one tomorrow and post some screenshots.
Thank you all for your help.
I am actually starting to like ACCESS.

Just to be safe, it is ok to use a combo box to number the tests 1,2,3,4,5 ? Don't need another table to store these values? The amount of thests is maximum 5 this is unlikely to change, usually its 1 or 2, maximum is 3. So 4 or 5 is being over precautious.

 

[plog]

Yes its fine to use a combo box. But that's a form design issue, you need to focus on fixing the problems with your tables.

 

[gemma-the-husky]

access actually resembles a series of spreadsheets, where each spreadsheet is linked to others by data common to both spreadsheets.

what you are trying to do is split your master spreadsheet into these sub-spreadsheets, as a first step.


In one post you asked about needing a programmer. The thing is that using access well is not like using a spreadsheet. you can do lots with a spreadsheet without ever using a macro. you can't do anything substantial in a database without code and macros. A large proportion of access development is actually DEFENSIVE. It is just so powerful that all users, including the developer, generally need to work in an insulated protected environment that limits them substantially to what they can do.

Access is hard enough for a non-programmer, and it's extremely hard for a non-programmer to develop anything substantial. It's much more advanced way of handling data compared with excel

It's like locking every excel cell, and then making them available again to a series of dedicated buttons or macros. It's not something any normal excel user is familiar with at all.

in particular you specify that each column of a database is of a particular data type, (say number) and you then cannot put anything other than a number in the cells in that column. No notes, no colours, Just the data. A date column will only accept a valid date. You can't accidentally put the wrong thing in. Finally, you can't refer to other rows of a database table as you often do with spreadsheets. Each row is complete as it stands, and you work with groups of rows as a whole.

It's complicated. hope this helps

 

[val_87]

Yes its fine to use a combo box. But that's a form design issue, you need to focus on fixing the problems with your tables.

I will delete the table i have now for testing.
I will replace it with.

Baby ID
test_nr
test_date
date_test_posted
postage_tracking_id
date_result_received
test_outcome - dropdown of outcomes including - retest required. I decided to eliminate Yes/No on retest required and put it in to test outcome.

 

[Minty]

Make sure you add the following

I will delete the table i have now for testing.
I will replace it with.

TestID - Primary key - Autonumber
Baby ID
test_nr
test_date
date_test_posted
postage_tracking_id
date_result_received
test_outcome - dropdown of outcomes including - retest required. I decided to eliminate Yes/No on retest required and put it in to test outcome.

The BabyID is the Foreign key, the TestID is simply a unique record identifier.

 

[val_87]

access actually resembles a series of spreadsheets, where each spreadsheet is linked to others by data common to both spreadsheets.

what you are trying to do is split your master spreadsheet into these sub-spreadsheets, as a first step.


In one post you asked about needing a programmer. The thing is that using access well is not like using a spreadsheet. you can do lots with a spreadsheet without ever using a macro. you can't do anything substantial in a database without code and macros. A large proportion of access development is actually DEFENSIVE. It is just so powerful that all users, including the developer, generally need to work in an insulated protected environment that limits them substantially to what they can do.

Access is hard enough for a non-programmer, and it's extremely hard for a non-programmer to develop anything substantial. It's much more advanced way of handling data compared with excel

It's like locking every excel cell, and then making them available again to a series of dedicated buttons or macros. It's not something any normal excel user is familiar with at all.

in particular you specify that each column of a database is of a particular data type, (say number) and you then cannot put anything other than a number in the cells in that column. No notes, no colours, Just the data. A date column will only accept a valid date. You can't accidentally put the wrong thing in. Finally, you can't refer to other rows of a database table as you often do with spreadsheets. Each row is complete as it stands, and you work with groups of rows as a whole.

It's complicated. hope this helps

I did 2 years of computer science, then however i moved on to a different speciality and graduated now. But it is all coming back to me, if you think about it, it makes sense. I will post more when i have done it. Hope you guys don't mind me asking a lot of stuff here!

Thanks once again!

 

[val_87]

Make sure you add the following

The BabyID is the Foreign key, the TestID is simply a unique record identifier.

So we are keeping test_nr and have also a TestID. TestID is a primary key and must be included and the BabyID is what ties the record together.

 

[Minty]

No the TestID IS the primary key.

To be honest I don't know if you really need your Test Number. If you include a test date you will always be able to sort the tests in the order they were done.

Incremental numbers that reset for every main record (EG baby1 test1, baby1 test2 etc) are normally a right royal pain to deal with, and actually serve no useful purpose most of the time. You can always give something a number in a report if it helps later, but as far as your data is concerned it's meaningless.

 

[val_87]

No the TestID IS the primary key.

To be honest I don't know if you really need your Test Number. If you include a test date you will always be able to sort the tests in the order they were done.

Incremental numbers that reset for every main record (EG baby1 test1, baby1 test2 etc) are normally a right royal pain to deal with, and actually serve no useful purpose most of the time. You can always give something a number in a report if it helps later, but as far as your data is concerned it's meaningless.

Thank you, i will give it thought tomorrow.
Someone said that it is hard for a non-programmer, but i am not doing anything liek Northwind I think my DB does not have that much data or functionality, so i should be able to get it done!

 

[gemma-the-husky]

Thank you, i will give it thought tomorrow.
Someone said that it is hard for a non-programmer, but i am not doing anything liek Northwind I think my DB does not have that much data or functionality, so i should be able to get it done!

val, it was me.

I don't think what you are trying to achieve is non-trivial. It sounds quite complex, and it also sounds like an environment in which it needs to work properly.

if you are designing for your own use, it's a bit easier. If it's for others to use, then you have to work to a "a fool can use this" design plan. Users will still find ways to surprise you. Access is still a windows app. Users can cut and paste data, print forms, send stuff as emails etc, lots of stuff you never thought of.

 

[Minty]

Nearly dave

Users will always find ways to surprise you.

 

[val_87]

val, it was me.

I don't think what you are trying to achieve is non-trivial. It sounds quite complex, and it also sounds like an environment in which it needs to work properly.

if you are designing for your own use, it's a bit easier. If it's for others to use, then you have to work to a "a fool can use this" design plan. Users will still find ways to surprise you. Access is still a windows app. Users can cut and paste data, print forms, send stuff as emails etc, lots of stuff you never thought of.

Well i want to get it done, with help or without help. I am interested and intreeged. And this is not a hospital system or anything like that. This will not manage births in a hospital... I would not go near to building anything like that, its a registar and at the minute 2 people are using a big book to write it in pen and paper... i want to make it electronic, that is all. I still think personal information and 1 to 3 tests results, is not that much data to store and find. Ill get thru! And yes, it will be mostly me working it!

 

[val_87]

Good day to you all!

Ok. It definetelly looks cleaner.
If you could help me out with relationships here. Because once the data structure and relationships are out of the way i can start playing with the part i like most!

Table personal_info has a primary key Baby ID.
Table clinical_info has also a primary key Baby ID and they are related. Now should i maybe have a different primary key in clinical_info and have Baby ID there as foreign key.

I followed your advice on testing and testing_t it has a primary key of Test ID and foreign key of Baby ID. How do i connect the relationships here with the personal_info and clinical_info.

Is it: personal_info (Baby ID) - clinical_info (Baby ID) - testing_t (Test ID). Again clinical info table comes to mind here, i feel something is a miss.

Maybe i should merge clinical and personal info in to a one table?
Iv attached both to this message, so feel free to yell at me, for constantly getting it wrong!

 

[Minty]

Okay it's better, but...

Looking at the Clinical data, as you have suggested it appears fixed per BabyID so no need to separate it into another table.

Doing this would remove the design problem - you have linked the Test results to the Clinical data. The tests should be linked to the PersonalInfo Table, and you have linked the BabyID to the test ID - this is not correct. You should have a one to many relation from Baby ID in Personal info to the BabyID in the test Table.

 

[val_87]

Okay it's better, but...

Looking at the Clinical data, as you have suggested it appears fixed per BabyID so no need to separate it into another table.

Doing this would remove the design problem - you have linked the Test results to the Clinical data. The tests should be linked to the PersonalInfo Table, and you have linked the BabyID to the test ID - this is not correct. You should have a one to many relation from Baby ID in Personal info to the BabyID in the test Table.

Thank you, thought that. I will merge the clinical and personal together, then do relationships as per your advice.

Then i can finally move on to forms. I know general form concept but there is some things i plan to achieve.

Have information input form, what do you think maybe one bigger form with subform on it as table for tests? Thing is fitting them together.

I know how to create a search usign query, what i don't know is how to create a search and be able to click on a record so it shows the record with tests on a form.
That is essentially all the functionality that i am trying to achieve... i promise after that i will leave you alone!

 

[Minty]

The basic construct would be a Personal Info form with the tests as a linked subform.

Sub-form Design wise is very much down to personal choice. Some would suggest a single form with all the data on it, personally I would probably have a basic continuous form with top level test info (Test Date, who did it , outcome) and maybe either a viewing pop_up or data entry form called from the sub form.

It really depends how much data is in the tests and what is going to work best for the users.

 

[val_87]

The basic construct would be a Personal Info form with the tests as a linked subform.

Sub-form Design wise is very much down to personal choice. Some would suggest a single form with all the data on it, personally I would probably have a basic continuous form with top level test info (Test Date, who did it , outcome) and maybe either a viewing pop_up or data entry form called from the sub form.

It really depends how much data is in the tests and what is going to work best for the users.

This is the new relationsips attached.

Now as far as design wise, here is what i plan to do.

1. As main form i plan to have a form that is like a table that would contain some information from personal_clinical table like:
   1. Baby UPI
   2. Baby DOB
   3. Mother Name
   4. Mother Surname
   5. Date of transfer to community.
2. On this main form footer i will have a search function, and a function to select and open the selected record in full. Main form will also have a function to add a new baby that will open in a form with testing subform.
3. When it will open it, it will oppen a form of a baby record with test table subform.

The consent table part in query and form comes up with 3 different slots for some reasons. But those can be deleted in form design.

How do i create this big table form? So its like a box with this information? For some reason when i created datasheet form it does not like to stick in a box....

Hop you understand what i mean.

 

[Minty]

In order to get the form layout you want you may need to use several subforms, but laid out to make them look like a main form and sub forms.

This method means you can get the forms side by side without having to get clever with the detail sections.

Draw out how you think you want it with what data, then build each form on it's own, then work on combining them.

 

[Minty]

Oh and I would rename the consent forms data - those / and spaces in names will give some headaches later on.