Basic Access Issue

[mimottershaw]

Dear All,

I have used this forum with great success in the past, and I am embarrassed to say that my current problem is so humiliatingly simple that I feel I should apologise for insulting your collective intelligence! However I don't seem able to solve it so here goes!:

I'm using Access 2007. I'm trying to write the simplest of simple databases. I want a table containing a unique person, with a unique identifiable number, but not a Serial ID, and also personal details such as name and DoB. I then want a linked table for that person ID containing details of "tests completed" on a particular date, with fields such as such as Test Date, Maths, English etc. In other words the same person could come back multiple times on different dates to complete the same or different tests. I want to enter details of a new visit where I enter the Person ID, and if they've been before their details are filled in, but if not I can enter new details, and then I enter the date of the test (defaulting to "today") and their tests completed.

Sounds simple right? I thought so too! I'm having a nightmare getting the tables to link as I wish and enter all the details without error messages. I think the problem is, as I'm not using a serial ID, for some reason the two tables have trouble communicating?? But that could be rubbish!?

As always I would be grateful for any help. The way I've got it at the moment I've got Person ID as a Unique (no duplicate) primary key, but as a text field not auto number. That is then linked to the same field (not primary key) in the other table, with a one to many relationship.

Would you believe I've written several databases in the past that are much more complicated, but for some reason this is baffling me!?!?!?!

Thanks in anticipation,

Mark.

 

[David Eagar]

As always I would be grateful for any help. The way I've got it at the moment I've got Person ID as a Unique (no duplicate) primary key, but as a text field not auto number. That is then linked to the same field (not primary key) in the other table, with a one to many relationship.


I would suggest that is your problem, it is only going to link to other text fields

 

[mimottershaw]

David, thanks for the response

So what should I do? The unique Identifier is alphanumeric and not serial or random so I don't have a choice? Is there a solution??

It seems ridiculously simple, but still seems tricky???

Mark.

 

[boblarson]

A couple of things...

1. You store the details of the person ONCE and ONLY once.

2. You don't have fields for each test, you have a table for tests and use ROWS of data for each test (using a JUNCTION table).

for example

tblPerson
PersonID - PK
FirstName
LastName
Address
City
State
PostCode
PhoneNumber
etc.

tblTests
TestID - Autonumber (PK)
TestDescription - Text

tblPersonTests
PersonTestID - Autonumber (PK)
PersonID - (FK)
TestID - Long Integer (FK)
TestDate - Date
Score - whatever...
etc.

3. Personally, I wouldn't count on PersonID being unique as it seems that something usually will happen somewhere down the line when something in the business rules which generates that ID will change and cause you a problem. I would still create a separate Autonumber field which you use for the Primary Key.

 

[mimottershaw]

Hi Bob. Thanks for replying. I am sure it doesn't need to be as complicated as that! Using your format, this is how it is set up at present:

tblPerson
PersonID - PK (Text)
FirstName
LastName
DoB

tblTests
Person ID
Test Date
Maths
English
Science

Person ID is linked one to many. Now, PersonID WILL always be unique. It is not generated by me. I don't need (or indeed want) a serial for the test unless absolutely necessary. At the moment Maths, English, Science etc are YES/NO fields.

I cannot understand why it is so difficult for me to be able to enter a new individual and the tests they sat, or add a new set of tests for a previous attendee for another day????

Thanks again,

Mark.

 

[boblarson]

Hi Bob. Thanks for replying. I am sure it doesn't need to be as complicated as that!

My example is not complicated, in fact it is NORMALIZED. Yours is NOT. You do not have separate fields of
Maths
English
Science

Person ID is linked one to many. Now, PersonID WILL always be unique. It is not generated by me. I don't need (or indeed want) a serial for the test unless absolutely necessary.

I've been doing this for 13 years. Trust me - use an autonumber for your primary key. You can include the PersonID that comes from whoever. But 2 years from now those people who generate it may come up with different rules that make continuing to use it impossible and then it all has to be redesigned. I know of which I speak. It is safer this way and then you NEVER need worry that the rules will change.

I cannot understand why it is so difficult for me to be able to enter a new individual and the tests they sat, or add a new set of tests for a previous attendee for another day????

Using my design it is not a problem to add new individuals or new tests or even different. Right now, you need to change your table and queries and forms if you added, for example - BASKET WEAVING to the list of
Maths
English
Science

and using my method you only need add a row in the the tests table.

 

[mimottershaw]

Hi Bob,

I didn't mean to cause any offence, and you clearly know far more about this than me. Would you mind if I emailed you privately to try and resolve this please? I have good reasons for requesting this; not just not wanting to display my ineptitude for all to see on this forum!

Regards,

Mark.

 

[boblarson]

I can't guarantee I'll have time to deal with it when at home, but you can try. I'll pm you an email address to use. And, really, it isn't a problem as far as posting things here. Nobody is laughing at you, nor will they (unless they are idiots anyway). We ALL have started out where you are. And part of the learning process is working through these various issues. One of the benefits of posting on the thread is you get a lot of help from a lot of knowledgeable people, and whereas my time is limited, you might get more help from other people who have also gone through this transformation.

We have a lot of great people here and some of them have only recently been in the position you are in. So, if you can, I would work on it here. But if necessary, we can try to do some things via email.

 

[mimottershaw]

Hi Bob,

It's nothing to do with my ego, honest! If I'm to be specific enough to get this resoved, then it can't be over an open forum. Can I pm you, and I'm sure you will understand when I explain.

Regards,

Mark.

 

[boblarson]

Hi Bob,

It's nothing to do with my ego, honest! If I'm to be specific enough to get this resoved, then it can't be over an open forum. Can I pm you, and I'm sure you will understand when I explain.

Regards,

Mark.

yes, you can.

 

[boblarson]

Here's the results.

 

[mimottershaw]

Thanks Bob. This looks really good, but I run upagainst the same problems that I had before. Namely, I can't add a new Patient at the same time as adding tests. This was the original issue, as the database has to be ultra-user-friendly, and be able to add a patient and new set of tests at the one time. There is also still the issue that the tests are yes/no.

I am so grateful for your assistance. To be honest I have been trying to come up with a solution in parallel to you as I am constrained by time, and had no right to expect you to share those constraints! I have a simplistic, non-normalised solution which I am sure would make your blood run cold, but it works of a fashion!!! If I could now only get the stupid qury to count my yes/no fields!!!! Grrrrrr!!!

Regards,

Mark.

 

[boblarson]

Thanks Bob. This looks really good, but I run upagainst the same problems that I had before. Namely, I can't add a new Patient at the same time as adding tests. This was the original issue, as the database has to be ultra-user-friendly, and be able to add a patient and new set of tests at the one time. There is also still the issue that the tests are yes/no.

I am so grateful for your assistance. To be honest I have been trying to come up with a solution in parallel to you as I am constrained by time, and had no right to expect you to share those constraints! I have a simplistic, non-normalised solution which I am sure would make your blood run cold, but it works of a fashion!!! If I could now only get the stupid qury to count my yes/no fields!!!! Grrrrrr!!!

Regards,

Mark.

Well, like I said in my last post. FIRST do the counts and THEN use that query inside another query to do the sorts and add extra fields.