Question Does access recognise capital letters?

[bite_tony]

When enforcing referential integrity, does access get angry about capital letters?

Or just in general, if an address gets put in with a capital letter in one instance and then without in another does it make a difference?

thanks for your time

 

[bob fitz]

does access get angry about capital letters?

I don't think so.

 

[mdlueck]

When enforcing referential integrity, does access get angry about capital letters?

What are you protecting with RI? That is usually used to sync columns with system generated numeric ID's to enforce that dependent records do not get orphaned.

You are not thinking to RI the actual data columns are you? As...

Or just in general, if an address gets put in with a capital letter in one instance and then without in another does it make a difference?

this sort of sounds like you are trying to perform RI on the data columns themselves.

 

[bite_tony]

Hi, thanks for your reply.
Maybe I am misunderstanding the use of RI..
I am very much a novice at this game..

Basically I have a customers/service calls database..
It has a table with customer details a table with service calls
The primary key in the customers details is the street address '10 high street' for example..
In the servicecalls table there is an auto number primary key and the street address is the foreign key in a one to many relationship (1 customer many calls)
I enforced RI on that relationship in the relationship bit of access to ensure that if other employees add service calls to the database they have to be linked to a customer street address..which is already present in the customers table

Am I going the wrong way?

 

[mdlueck]

The primary key in the customers details is the street address '10 high street' for example..

If you use the addy as the key value to be repeated in other tables and protect with RI, then you can never update the addy IN ANY WAY else you break the relationship. BAD DESIGN!

Use an autonumber column scheme in the primary table, and in the dependant tables copy the autonumber value and "point back to what record you mean" in the primary table. Have address be a supporting field of the "master" table if you like... just DO NOT perform RI on that value!!! Perform RI on some "meaningless" system generated number. MUCH BETTER in the short and long run!

 

[bite_tony]

Thanks for your input.

please bear in mind Im still trying to work access out before tutting at my silly question but:

If I have an autonumber as the PI in the customers table, would someone inputting servicecall details need to know the customers autoID in order to link the call to a customer, or could i still use the street address to link the two?

My thinking behind using the Address as the PI is that there is no real reason that the street address should change, we are a double glazing company. So the address of a property is pretty central to the customers order.
If they changed address it would go on as a new customer (i think)

 

[mdlueck]

If I have an autonumber as the PI in the customers table, would someone inputting servicecall details need to know the customers autoID in order to link the call to a customer, or could i still use the street address to link the two?

UI workflow should be: Open customer record, add a new call record in the context of said customer. Thus the AddCall will know the ID of the customer the new record is being created and associated with.

 

[bite_tony]

should i maintain my two tables?
and would my link between the tables still be the street address?

 

[RainLover]

People are suggesting that you use an Autonumber as the Primary Key.

I would go further and say that you should always use Autonumber.

 

[bite_tony]

People are suggesting that you use an Autonumber as the Primary Key.

I would go further and say that you should always use Autonumber.

I understand the logic of that,
However, i don't understand if a fellow employee would need to know the customers autonumber to attatch information to that customer.

Basically, if an employee is on the phone to an irate customer and is trying to log a call, could they use the street address (which is information they would have to hand) to add details to that customer, or would they have to find the customer record, ansd then the autonumber and then go on to add details of a call?

 

[mdlueck]

should i maintain my two tables?

Should maintain all tables.

If you are asking should you do your own RI in VBA code, no. Let the DB take care of RI. You just build DB schema which is efficient and elegant... not duplicating data needlessly.

and would my link between the tables still be the street address?

No, have an autonumber field on the "main" table. Refer back to the correct lookup autonumber in the "main" table from "supporting" tables. Let those supporting tables have their own autonumer ID field so that UPDATE statements have a key in those tables to do the update. If those supporting tables do not have further supporting tables which will require an ID number to perform RI, that is OK... still need a unique ID in order to perform SQL UPDATE statements, so "always"* include an autonumber field as the first field and set that as the primary key for the table.

* There are times NOT to use autonumber keys... such as when being fed unique records from another database system where the autonumber field is not provided/exposed to you. Example, I have one table which is fed BOM (Build of Material) information from an ERP system. My table which receives the data has no autonumber column, and has a three column key: ParentPartNumber + ChildPartNumber + SequenceNumber as those are the fields which must be a unique combination per the ERP support team.

 

[mdlueck]

However, i don't understand if a fellow employee would need to know the customers autonumber to attatch information to that customer.

No... Please re-read my post...

UI workflow should be: Open customer record, add a new call record in the context of said customer. Thus the AddCall will know the ID of the customer the new record is being created and associated with.

 

[bite_tony]

If you are asking should you do your own RI in VBA code, no.

I think you seriously over estimate my access capabilities....


*goes to find to find out how to 'refer back to the correct autonumber'

 

[RainLover]

The answer to your problem lies in understanding the use of Combo Boxes.

With a Combo box you can search Address, Client Name or anything else. However the Primary key is part of the Combo, just hidden. So although you search a Name the work is all done by the hidden Primary Key.

I don't have a Link at hand so do a Google on Combo Boxes. You could also look at the Northwind Database You can even search this Forum.

It you go to my Sky Drive Link in my signature you will find "Inventory Control" This Database uses a few Combo Boxes. Download it and look at te workings. Look at the properties of the Combo especially Column Widths, Source Control and Row Source.

Hope this helps.

 

[bite_tony]

I tried to thank you all for your help, but it won't let me for some reason...