Problems when creating a database

[SQLBungler]

I'm writing a small database but am having problems with creating the last table. I've uploaded my DB here:

http://www.alchemistresearch.com/0306483.mdb

and am creating the tables in the following order:

FacilityType
Client
Visit
Equipment
Assessment
Usage

It's when I'm creating the Usage table that I have an error in setting the foreign key for "equipment_reference_number". I just can't see where the error is, I hope someone else can!

Thanks.

 

[Blorf]

Hi.

I don't know why, this makes no sense what so ever, but...

If the table Usage exists, then your tblUsage over writes it, and works just fine. Otherwise you get the error.

I think we need someone smarter than me to explain, but your work around is to create Usage with no keys, and use your sql to create and over write it.

ChaZ

 

[SQLBungler]

I'm glad it's not me. A colleague has also had the same problem.

The only problem I have now is that Access says that table "Usage" already exists after setting up a command to create "Usage" with no keys like you said. Would I need to alter the second create to ALTER?

 

[Blorf]

If you launch with a Macro, you can set warnings to no, and it will over write with out asking.

Still would like someday to know what the cause is though.

ChaZ

 

[SQLBungler]

I've asked the project leader to take a look at it tomorrow. I'll post any feedback I get.

 

[SQLBungler]

I can't get it to launch with a macro like you said.

 

[PHV]

Your problem is here:
CONSTRAINT fk1usage FOREIGN KEY (date_of_visit) REFERENCES Visit,
As the PK of the Visit table is a composite key, you should code this:
CONSTRAINT fk1usage FOREIGN KEY (client_number,date_of_visit,start_time) REFERENCES Visit,

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLBungler]

Thanks, that worked perfectly! I have never created a 'composite key' before, only simple primary and foreign ones.

Looking at my ER diagram now, I can clearly see where the problem was. I am forever in your debt!

 

[SQLBungler]

I'm back!

I'm now having trouble using SQL to enter data into the table "Usage". The error message I get is a "type conversion failure".

The data I am enetering is:

INSERT INTO Usage VALUES (1,"11/12/04","15:00","16:00",1)

I've tried putting the data into different orders to no avail.

 

[GingerR]

i looked on line and unless you've changed something, your table has these fields in this order:

start_time TIME,
end_time TIME,
date_of_visit DATE,
client_number INTEGER,
equipment_reference_number INTEGER

but your INSERT INTO statement doesn't have items listed in that order.

 

[PHV]

And for DateTime literal constant use this syntax:
#11/12/04#,#15:00#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLBungler]

Hi again,

Sorry for the delay in replying, I've had a break from the project.

GingerR - I was aware that the order of the fields was wrong. I had made the changes but not altered the uploaded file, but still had problems.

I'm wondering if I should start again from scratch. My original ER diagram is here:

http://www.alchemistresearch.com/diagram.jpg

Any advice would be most appreciated.

Andy.