Relational Database structure advice

[verplexd]

Hello All,

I haven't worked with relational databases since college (about 4 years ago) and I would like some input on a database structure that I have come up with. Any input is welcome. Well except for stuff like "go back to school" and "read a book".

Here is what I have so far:

Thanks,
Jason

 

[jrbarnett]

Looks alright to start with, but tblkStateAbbs isn't linked to anything - is there a reason for this?

Are you having any problems with it that need fixing?

John

 

[verplexd]

Thanks for repying John,

I just forgot to add the relationships for tblkState.

The problem that I'm getting is on tblOrders. When I try to create an AutoForm for that table I'm getting the error:

&quot;This expression is typed incorrectly, or it is too complex to be evaluated. <snip>&quot;

I didn't have that problem with tblCompanies which has a very similar structure. Maybe it's the tblkSuppliers that is causing the problem?
It should be a One-To-One relationship, but it is shown to have a One-To-Many.

 

[jrbarnett]

I have never had a wizard fail to run a fairly simple form such as your Autoform.
It seems as though that message relates to the VBA code - take a look at the underlying module code and see what is going on as well.

By the way which version of access are you using?

John

 

[verplexd]

It's Access 2000.
I haven't even started to code anything yet.
I think it may be a problem with the SupplierID... It's supposed to be a One-To-One but it's listed as a One-To-Many.
The &quot;tblOrders.strSupplier To tblkSuppliers.pkSupplierID&quot; relationship, that is.
Is there a way to change it? I haven't been able to figure it out.

 

[jrbarnett]

If the supplierid is a 1:1 relationship with an order, it means that one supplier can only have one order. Are you sure that is right?

To change the relationship type, right click on the black line linking the tables, choose properties and change from there.

By the way there may be some VBA code already - the wizards generally put some junk in without you asking to do it.

 

[verplexd]

John,
You are right to question my logic about the relationship.
One order only has one supplier, but a supplier can have multiple orders.
I was able to find the problem with the damn autoform not working. I had diferent data types of each of the connecting fields. Stupid mistake, but what's even worse is that I have made the same one before.

 

[verplexd]

Hey John,

I have a question for you...

In my picture above I have tblQuotes.strUser linked to tblkUsers.pkUserNumber
If tblkUsers.pkUserNumber is an autoNumber then I should make tblQuotes.strUser a number as well, right?

 

[lespaul]

Yes the srtUser will need to be a number as well, and I think it has to be a LONG INTEGER.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[verplexd]

Thanks Leslie... and yes the AutoNumber is a long integer.

So when I use the field (lookup) in a form, I can make it show the name instead of the AutoNumber, right?

 

[lespaul]

Yes, you can show the user's name instead of the number. Depending on how you are looking it up (query, table, etc), I haven't done much in Access lately, I'd have to look into it to tell you exactly how it's done. You may want to look in the Access Forum for some specific details.

HTH
Leslie

 

[verplexd]

Okay cool... I was just making sure that I get started off on the right foot. It's such a pain to change the structure of the Database after the initial setup. Hopefully I will figure out the rest as I go. If not, I'll submit my posts to the Access forum.

Thanks for your help Leslie and John.