Creating Primary Key and defining field properties

[Wantok52]

Hi,

I have a database that records assests for a number of customers. Each time a new customer in created I want to create a new Asset Table for that customer. I have worked out how to create the tables, and think I can create the relationships, but am not sure how to set the field properties e.g. size and any input masks etc. (I can set the type OK).

What I am doing is creating a table called "Assets", then renaming the table to suit the Customer eg "Joe Blogs Assets". I want to set the relationships, field properties and Primary Keys after renaming. These properties will be the same for every table.

Thanks,

Marcus

 

[Remou]

Perhaps you could explain a little more? Why not keep a template table and copy it? Why a new tabe at all, rather than a customer code in a 'master' assets table?

You can use Alter table to change some properties and add others:
[tt]strSQL = "ALTER TABLE tblStudent ADD CONSTRAINT EmployeesOrders " _
& "FOREIGN KEY (Mother) REFERENCES tblParent (Code) "
DoCmd.RunSQL strSQL[/tt]

[tt]SQL = "ALTER TABLE table1 ALTER COLUMN Field1 INT"
DoCmd.RunSQL SQL[/tt]

Then there is the CreateRelation Method.

 

[Wantok52]

Hi Remou,

Thanks for your reply.

I was using only one table for Assets, and was using a combination of Customer Code and Asset Numbers, and incrementing the numerical part only each time an Asset was added.

If I revert to numbers only, and keep a separate "Master" Assets table, I will have several entries in the Assets table withe the same number, as each Customer would have Aseets with the same Asset Number.

Since posting the question I have worked out how to do it (quite simple realy)

All I needed was to bracket the field size after the field type, and CONSTRAINT to make the Master Key in the SQL statement e.g.

CREATE TABLE Assets (AssetNumber TEXT(9) CONSTRAINT MyFieldConstraint PRIMARY KEY, Location TEXT(50), Manufacturer TEXT(50), EquipmentType TEXT(50), ModelNumber TEXT(50), SerialNumber TEXT(50));

I can see your solution would also work.

Thank once again,

Marcus

 

[Remou]

I'm glad you have sorted out an answer. I am still puzzling about the master table; sheer curiosity. No customer has the same asset twice, though several customers may have the same assets, right? So a primary key of
CustomerID + AssetID would be unique, wouldn't it?

I feel that creating a new table for each customer could become a problem quite quickly ...

 

[Wantok52]

Hi Remou,

Yes, I think you are right. I've had a re-think and will go back to my original table.

In the table I have the AssetNumber like; MBHS-0230, where the numerical part repesents the Customer ID and the numrical part the Asset number. I have written code to increment the numerical section only, thus making creating new Asset Numbers automatic.

I originally had CustomerID and AssetNumber linked as a primary key, but that wouldn't let me automatically update the Asset Numbers.

These Asset Numbers are really for my own use. the Customers may have Asset Numbers of their own.

The time I spent was not really wasted though; I'm always learning new things about Access.

Cheers,

Marcus

 

[BobRodes]

This sounds a bit rough, if you should need to reassign an asset to a different customer. Will that never come up?

Bob

 

[Wantok52]

Hi Bob,

It is never likely that I would reassign an asset to a diferent customer. The assets are owned by the customers, so that if as asset were to transfer from one customer to another through a sale or whatever, I would add the asset to the new customer as a new asset. The old asset for the old customer would still have the asset recorded, but with a note saying the asset has been disposed of.

 

[BobRodes]

Here's one more thing. If you want to keep customer information, such as name, address, whatever, you're keeping it redundantly for each asset the customer owns. This is a violation of second normal form, which says that all fields in a table should be dependent on the primary key.

Here's a properly normalized version of your database structure.

Assets table:
AssetID - Primary key
CustomerID - Foreign key
Location, Manufacturer, Equipment Type, IsDisposed, etc.

Customer table:
CustomerID - Primary key
Name, Address, City, State, Zip, etc.

This is the most economical way to store data, since when you have multiple assets for one customer, all you store multiple times is the customer id. Furthermore, if you ever did need to reassign an asset (things change...) all you would need to do is change the customerid field for that asset record.

In your arrangement, you can run into real problems when a customer changes address, for example. If you don't keep contact information, well, a customer can change a name, too. Point is, you only want to store a customer's information (called "instance data") in one place, or you may wind up having to change it in multiple places. I can guarantee you that that's a recipe for trouble.

HTH

Bob

 

[Wantok52]

Hi Rob,

I fully understand your database structure and my structure is essentially the same.

This is my database structure'

tblAssetRegister
AssetNumber PrimaryKey
CustomerID ForeignKey
Location
Manufacturer
EquipmentType
ModelNumber
SerialNumber
AssetNotes

tblCustomers
CustomerID PrimaryKey
CompanyName
StreetAddress
TownorSuburb
State
PostCode
ContactName
PhoneNumber
FaxNumber
EmailAddress
Notes

I may not have made myself quite clear. Since registering the original post I have reverted to this original structure.

I do not want to transfer Assets between Customers by changing the CustomerID field only as this may result in a Customer having assets with numbers all over the place, for example the first assets may be 0001 through 0167, then if they obtained an asset from another Customer, the next asset may be 0967. This format would give no indication as to the number of assets a Customer has on my records.

My AssetNumber is a combination of the CustomerID and a numerical number. e.g. MARD-0024. When I add a new asset I want the asset number to increment to the next number, and I am incrementing the numerical part of the AssetNumber only. This occurs via a form AddNewAsset, using a Private Function, IncrementNumPartOnly.

Please note that the AssetNumber is still unique, and is actually text. This way I can have effectively have an asset for Customer MARD as MARD-0025, and an asset for Customer MBHS as MBHS-0025, and am able to determine quickly the number of assets any customer has. Also, the only thing stored multiple times is the CustomerID.

It does not matter if a Customer disposes of an asset; all I would do then is record in the AssetNotes field that the asset had been disposed of. If it goes to another of my customers it is added as a new asset for that customer. As this situation is very rare, it is really of little consequence

I believe this structure does not violate the second normal form, as all fields in the table tblAssetRegister are dependent on the primary key.

Regards,

Marcus

 

[BobRodes]

<I believe this structure does not violate the second normal form

I agree. I understood you to be using one table.

<This format would give no indication as to the number of assets a Customer has on my records.

If you try to keep things in this sort of order, you're likely to find times when you want more flexibility and can't have it. More typical would be to not worry about that, and build queries that give you the information that you're looking for. If you ever want to know, to take your example, how many assets a customer has, you can either do something like SELECT COUNT(AssetNumber) FROM tblAssetNumber WHERE CustomerID = nnnn, and get a count. Or, you could simply do SELECT * FROM tblCustomers WHERE CustomerID = nnnn, and get a table for a given customer, whose record count will be easy to see. You can save whatever queries you want, and open them as you would tables.

I suspect that some of what I'm saying is old news, but nonetheless, it's what's customary. While your plan works, it's not so easy to maintain over time, and it's the sort of plan that will be hard for other people to figure out if you're not there, since it's atypical in terms of practice.

Bob

 

[Wantok52]

Hi Rob,

Thanks for your reply.

I believe that my plan is quite flexible. The Customer ID is determined by the person adding the customer to the database, and is four characters, all in uppercase. Even if an operator enter the CustomerID in lower case, it is stored as upper case.

Organizatiios usually like to keep Asset records in sequential order, the only difference is that in this case it is preceded by a four character string. This is all done automatically via a form, so the operator doesn't enter the Asset Number.

I do have several queries connected to forms which present data on selected Customers in many ways, mostly in the form of Reports, which can then be printed.

I want to maintain sequential numbering for individual Customers. That way when I present a Report to a Customer they would not get confused by having an asset with an Asset Number of, say 3657, when they have only 200 Assets.

I disagree with your comments regarding long term maintenence, and difficulty in other users figuring it out. There is nothing really to figure out,Asset Numbers are generated automatically, and are in sequential order for each Customer.Even the first Asset Number is generated in this way.

 

[BobRodes]

As you will.

Bob