Addition to database design question

[Annelie]

I have a going database for a contractor which tracks jobcost, mostly from
imported data from QuickBooks, but actual payroll is also entered.
Everything is working quite well.

The client is using Act to track bids for new jobs, and eventually some bids
gets turned into a job. Since detail information about the job is held in
Access, I want to add the bid part to the access database and abandon Act.

But I am not sure how to set up the bid table. In Act, the bid is tracked by
name and when a bid turns into a job, the job number is added in the job
number field and the job is moved from the bid group to the jobs group.

In access, I don't think it is a good idea to track the job by name, perhaps
an autonumber field could be the primary key.
I thought perhaps to have a job number field in the table and when that
field is filled, it will trigger a macro which will add the job to the jobs
table and delete it from the bids table. Is that a good approach?
Annelie

 

[PC Datasheet]

Why not just have one table, TblBidJob, with a field named BidOrJob. When a bid
first comes in mark the field "Bid". If the Bid turns into a job, update the
field to "Job". At some point you could delete bids that didn't become jobs
prior to a certain date.

 

[Annelie]

Very tempting thought, but the job number is my primary key, so I must have
a job number.
Annelie

 

[PC Datasheet]

Assign a job number to bids. If a bid never becomes a job, the job number will
never be used anywhere in your database.

 

[Guest]

PC Datasheet's suggestion has merit. It brings up the concept of "surrogate keys". Google for it and you'll find plenty of information that will, hopefully, persuade you to use them.

What it all boils down to is that while the Job Number might appear to be a good primary key, it's still something that is subject to change...even VITAL pieces of information get mistyped or miscommunicated from time-to-time. Now, when the inevitable error occurs, if you've used the JobNumber as your primary key, you're going to have to track down every occurrence of the offending job number in every table and update it. Depending on how complex your database gets, that could be a real job.

If you've used an autonumber as your primary key, however, you only have to change the job number once and it won't affect the relationships with the other related tables.....because what they're joined on is the autonumber, which you didn't have to change. What's really nice is that you can still make the job number a required field. You can make it an index to speed searches. You can even specify that it must be unique. You'll get all the benefits of having it as a primary key without the possibility of having to do a WHOLE BUNCH of find and replace operations that may even force you to delete and recreate your relationships to do.

If you do make the job number field required, you can assign a bid number in the job number field until it becomes a job. If you configure the bid number in a consistent manner like making all bid numbers start with "B", for instance, you've provided yourself an easy mechanism for finding all of the bids very rapidly by being able to filter for B* on the job number field OR querying for LIKE "B*" in a query.....provided, of course, that legitimate job numbers never start with B.

I don't mean to sound preachy about it, but as I'm currently looking at having to modify upwards of 300K records because I chose not to use them in a certain situation in my work where I would have sworn the values would NEVER change and it was darn convenient to have them in the tables to keep me from having to do an extra join on just about every query I have to write....I'm feeling a lot of love for surrogate keys at the moment.

Good luck,

Fred D

 

[Annelie]

Thanks to both of you,
Fred's detail explanation is convincing me to do what PcDatasheet suggested,
assign an autonumber and a job number. Needless to say I have to go through
every query, form and report to make the changes, but better now then later.
I can see it coming, having a job number 2554A and B, it can happen and
right now it is a numeric field only and I need to change it anyway.

I am still a bit confused as to what to do with a number system for the
bids. Right now they are tracked by name. If the last job number was 1862,
when the bid turns into a job, it is then assigned the next job number,
which would be 1863. The job number needs to be unique, without duplicates.

What do you thing about leaving job numbers numeric and using alpha-numeric
for bids, like most bookkeeping programs, use the first 3 letter of the
first word, the first letter of the second word and two numeric digits. That
might be the solution. That way I can easily exclude bid from job costing.
Of course that might get me into trouble with a job name which contains a
street address.
I guess I have to roll that around in my head for a few days.

Annelie

 

[Guest]

When it comes to numbers versus text questions, I ask myself the following questions:

Will I ever need to do a math operation that involves the value of this field?

Will it ever be important that I'm able to sort this field as a number rather than alphabetical? If so, how often will that be important?

If I can answer 'no' to both questions, then I'll usually make the field a text field. Otherwise, I make it a number.

If you've got the luxury of being able to assign the job number yourself, you might just use the date formatted like 20040723. If business is really doing well and you're getting multiple jobs in a single day, 20040723A, 20040723B, 20040723C, etc. You get the idea. It's a simple system that will generally ensure easy unique identifiers while also providing some meaningful information. And it sorts nicely. Since you've set the field to only accept unique identifiers, you can let Access worry about whether it's unique and just add an A, B, or C... as the situation demands. You could just keep adding different letters until you hit on a unique combination or use a combobox with all of the current Job Numbers sorted from most recent down, so you've got the last one used at your fingertips. If you're up to getting into some VBA coding, writing a routine to come up with the next number in this format would be a fairly straight forward process.

We use a variation of this process for naming projects where I work and it works very well.

Just some thoughts,

Fred D

 

[Guest]

When it comes to the question of text vs. number, I ask myself the following questions:

Will I ever need to use this field in a mathematical operation?
Will it ever be important that I be able to sort this field as a number?

If I can answer 'no' to both questions, I make the field a text field. There are other considerations, if you're dealing with large volumes of data, but for small databases those issues aren't really significant.

A system we use where I work that works very well for assigning unique identifiers for new projects works like this:

First project of the year = 2004-0001
Second project of the year = 2004-0002
Third project of the year = 2004-0003

We might actually break 100 new projects this year, but even if the growth rate continues we're safe on the number of studies the system allows each year for a long time. I've got a VBA routine that calculates and assigns the next Project ID everytime new a projects gets entered, but if you're not up to coding, you could figure the next number in the sequence in your head pretty quickly. Or you could go with something even more straightforward like using the current date formatted as 20040723. If you get more than one job in a day, 20040723A, 20040723B, etc. You could handle bid numbers in much the same way, B-20040723A, B-20040723B, etc. And when the bid becomes a job just delete the 'B-' or assign it a new number based on the date.

This is the fun stuff

Fred D

 

[Annelie]

Unfortunately, I cannot let the system assign a job number, but I think I
can write enough VBA to let the system assign a bid number.
Thank you very much for all your input. No, I will never have to use the job
no field for a calculation, but I do need to sort by job number.
Interestingly, I add the project year to each job via query, based on the
start date of the job, which is not necessarily the same year then the bid
award.
Annelie

When it comes to the question of text vs. number, I ask myself the following questions:

Will I ever need to use this field in a mathematical operation?
Will it ever be important that I be able to sort this field as a number?

If I can answer 'no' to both questions, I make the field a text field.

There are other considerations, if you're dealing with large volumes of
data, but for small databases those issues aren't really significant.

A system we use where I work that works very well for assigning unique

identifiers for new projects works like this:

First project of the year = 2004-0001
Second project of the year = 2004-0002
Third project of the year = 2004-0003

We might actually break 100 new projects this year, but even if the growth

rate continues we're safe on the number of studies the system allows each
year for a long time. I've got a VBA routine that calculates and assigns
the next Project ID everytime new a projects gets entered, but if you're not
up to coding, you could figure the next number in the sequence in your head
pretty quickly. Or you could go with something even more straightforward
like using the current date formatted as 20040723. If you get more than one
job in a day, 20040723A, 20040723B, etc. You could handle bid numbers in
much the same way, B-20040723A, B-20040723B, etc. And when the bid becomes
a job just delete the 'B-' or assign it a new number based on the date.