Hot Question

[asarak]

Hi again,

i have a db with 6 main tables named

1. tbl_main_data
2. tbl_habits_data
3. tbl_medical_history_data
4. tbl_medication_data
5. tbl_osteo_data
6. tbl_woman_data

the 1st table tbl_main_data has the field "sn" as key, and it is indexed with no duplicates,and its not auto_increment.
There is no relation in tables with relationships but every table has the "sn" as a number.

in Forms now which is tabs with subforms i have made the "connections" between the tables i mentioned above.

the main problem is :

while i was trying to input data sometimes i take the following message
" the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."

after that if i press ESCAPE that clears the fields, close and reopen the access and go to the specific record and the specific subform and enter the data, working fine!!!!!

I have to say that i dont wont to have a 1 to many relationship in these tables, i prefer to have 1 to 1.

the forms has also text boxes and frames.

I dont know what to do,

please any assist??

 

[ColinEssex]

Use an AutoNumber as the ID in the main table use it for nothing else other than a link to the other tables.

Do the relationships properly in the relationships window.

Col

 

[asarak]

another try

in a previous version that i have also no relationships but

1. tbl_main_data has the "sn" field as indexed with no duplicates
2. tbl_habits_data has the "sn" field no indexed
3. tbl_medical_history_data has the "sn" field no indexed
4. tbl_medication_data has the "sn" field as indexed with no duplicates
5. tbl_osteo_data has the "sn" field no indexed and also the final
6. tbl_woman_data has the "sn" field no indexed

,

working fine with no problem in insert!!!!!
but creates multiple rows in the tables
No 2,4,5
But when i am looking in these subforms i cannot see the data!!!!
tha data exists in database fully.


i am trying to remove any duplicate rows and change all sn's into indexed with no duplicates and make the relationships as
1.sn --> 2.sn --> 3.sn --> 4.sn --> 5.sn --> 6.sn
numbers are the tables above...
is that correct or to make the relantionships as
1.sn --> all other tables.sn ???

 

[asarak]

in any of the cases the same problems...
any assist???

 

[neileg]

Why do you want a series of one to one relationships? Why not put it all in one table?

 

[asarak]

the fields are more than 256 fields. So .... :-(

 

[neileg]

256 fields sounds like a normalisation problem. Can you post your table details?

 

[asarak]

the whole db has 285 fields includes the "sn"
also i have 29 other tables that i use them in combo boxes... which works ine.

the main has 81 fields
the habits has 42
the med hist has 45
the medication has 48
the osteo has 50
the woman has 19.

 

[ColinEssex]

You do have a normalisation problem. Sounds like you are using the tables like you would a spreadsheet.

Look up 'normalisation'.

It should be very rare for a table to have any more than 20 fields tops.

Col

 

[asarak]

possible yes, but i have at this time my client want to run it asap.
thanks a lot, if i send you the db could you please checkit???


thanks

 

[ColinEssex]

post it on the forum if you like and we'll have a look, but I'm doubtful. I know what it will look like, we've seen it dozens of times here.
If you're going to use Access, you need to do it properly - Access is not a spreadsheet and if you use it as such you will run into huge problems when trying to interrogate the data for reports.

Your client should wait - better to have a proper job done than one which will fail and give continual problems.

Col

 

[asarak]

Colin you are absolutely right.
i am at the same side, and i am going to have a discussion to fix some things.
anyway... do you mind to mail me in asarak@bioiatriki.gr

regards ASARAK

 

[ColinEssex]

Post it on the forum then others can see it too and maybe offer an opinion.

Col

 

[ColinEssex]

It looks like (from your first post) that your database is regarding osteoporosis. If this is the case, then its a pretty standard format you need. Presumably you'll need to record test results like the BMI, ultrasound scans and their associated results.

You need a patient demographic table - for example, whats the difference between your main data table and your woman table?

Col

 

[asarak]

and the mdb is....

ok Colin thanks all of you

also you need the mousehook.dll in the same directory with the mdb

i use it to disable the mousewheel record rotation.

thanks

 

[neileg]

Very, very un-normalised structure. As we suggested, your date is wide and short. It should be long and thin. You have several sets of repeating groups like in the medication data. You also have areas where you are using several fields to capture one piece of data when it should be one field with a range of values, eg physical activity.

 

[ColinEssex]

Thanks Neil, I downloaded it but haven't looked yet.

Col

 

[asarak]

hi again guys and many thanks for your time.
first, about the date, this short date in some fields is what the clinet wants.
second, the main problem of multiple fields which have the same data to capture is that it could be someone to have more than one medication data, that is also to someothers fields like "osteoporosis".

about the normalization you are right.
by the way, you asked about the diferrence between main_Data and woman_data, the woman_data must be enabled to get values only when the main_data.sex is 2, this is the default as the clinet wants.
One more question, is it good to make a procedure to initiate values to all tables, while i p.e. lost focus in SN???? or it is not a good thought ???

thanks a lot for your time.

Regards

ASARAK

 

[neileg]

hi again guys and many thanks for your time.
first, about the date, this short date in some fields is what the clinet wants.

If you client understands relational database design, let the client develop the database. Users never see data in tables so it makes no difference how it is stored. They see the data in forms or reports and you apply the formatting there.

second, the main problem of multiple fields which have the same data to capture is that it could be someone to have more than one medication data, that is also to someothers fields like "osteoporosis".

Yes, we know, but you don't do it by having lots of fields, you do it by having multiple records in a related table.

about the normalization you are right.

I think you don't really understand normailsation. It took me some time to figure this out, but now it's obvious. I learned what I know from these forums so do some reading up.

by the way, you asked about the diferrence between main_Data and woman_data, the woman_data must be enabled to get values only when the main_data.sex is 2, this is the default as the clinet wants.

As I said, the client doesn't see the tables so it's not their business to specify the table structure.

One more question, is it good to make a procedure to initiate values to all tables, while i p.e. lost focus in SN???? or it is not a good thought ???

I'm sorry, I don't understand the question.

 

[asarak]

I mean, if it is logical to create with VBA code in order to initiate values in all fields in these 6 tables while i insert the main key (SN).
Is that clear??? or else i have to go for English lessons!!!!!

thanks you all