Access Database problems

[ShotokanTuning]

I'm using a Access database and I want to create something like this:

A Table called Account with primary key: CompanyName.
A Table called VisitReport with a primary key that's formed out of three fields: Date, CompanyName, WrittenBy.

The CompanyName must be an existing Account(CompanyName).

I've made a relationship between Account(CompanyName) and ViistReport(CompanyName). For the table VisitReport I've set the three fields mentioned earlier as primary key. So all three fields have that little key symbol infront of them.

Now the filed WrittenBy could be an empty field, but the Access database won't allow it to be empty because it's set as a primary key.

What I'm trying to do is use three fields together as the primary key and allow it if Writtenby is empty.

How to fix this problem?

[Ecniv]

You can't.
At least not with that table layout.

An option :
tblVisitReport
VisitReportID - Autonumber - pk
VisitDate - date
CompanyName - text (allowzero)
WrittenBy - text (allowzero) (or number and use a look up table)

But this means possible multiple records of date and companyname, so you'd need to check for this in the front end of the program.

Consider this scenario tho:
Man visits company on business, concludes in the morning, goes to lunch (signing out) Comes back at 3pm with more info and another meeting, needs to sign in again. Would your system cope?


Vince

[ShotokanTuning]

So that's why I didn't know how to do it...

It's not possible.

Hmmm...

What use are primary keys?

What use have relations?

If in fact you check all that in your application.

Are they mere back-up plans for if your application's checking fails?


Another question what happens if I drop any primary key for the VisitReport table. Then set a Visit Report(CompanyName) and Account(CompanyName) relation? Will that work and if so what are the disadvantage?

Thank you Vince.

[Ecniv]

Originally posted by ShotokanTuning
So that's why I didn't know how to do it...

It's not possible.

Hmmm...

What use are primary keys?

Unique record identifiers - allows you to get at the one record instead of a load of 'em

What use have relations?

Referential integrity - for beginners - meant to make sure that the data in the db is correct and relates to something and is not just hanging around.
Personally I think its the developers position to install this referential integrity via the data entry forms/views.

If in fact you check all that in your application.

Are they mere back-up plans for if your application's checking fails?


Another question what happens if I drop any primary key for the VisitReport table. Then set a Visit Report(CompanyName) and Account(CompanyName) relation? Will that work and if so what are the disadvantage?

Thank you Vince.

Only problem would be if (as in my scenario in the previous post) a person visited, signed out and tried to resign back in on the same day, it would allow you to store the day and company only once. Multiple people from the company signing in together may present similar problems.

What are you trying to set up/achieve?


Vince

[ShotokanTuning]

Yeah but if you check if the entered data combination (the three fields) is unique, then why label it as primary key. You can use it as identification without it being a primary key. So what is the extra value of labelling it a primary key, except for as an extra backup for if your application's input checking fails.

An answer to your question: An Account is used as a term for a Customer, when someone visits this customer he makes a Visit Report. If he visits this customer twice on the same day (rare) it would fit under the same Visit, he'd just have to update the previously entered visit.