Hello all! This is my first post on this here fine forum, and im hoping to get some help!

When i started at my job 3 years ago, i took it upon myself to convert the job book into electronic format. I knocked up an excel spreadsheet, and ever since then, any job we've quoted has gone into this spreadsheet. It's now quite big, around 5mb in size, and is becoming a nightmare to handle / search / open!

Somebody told me to put the lot into an Access database, and then write queries to help it along! Sounded easy enough!

Now that i've imported all my data into Access, i have a few questions!

1) Primary Keys. I'd like to have the job number as a primary key. Our job numbers are individual 4digit numbers and go up in 1number increments. But the spreadsheet starts at 4595, and as hard as i try, i cannot get the Primary Key to start at 4595. Any ideas?

2) Date's. On the spreadsheet, I'd have 4 cells. The first had the date we recieved a quote. The second cell displayed the return date for the quote. The Third cell then showed the date we returned the quote. And the forth cell, had a formula, along with some conditional formatting, which showed whether the tender was returned in time or not..

When importing my spreadsheet into Access, all the dates went into a strange format. The number is a 5 digit number, and typically looks like '39105', (which i beleive is the number of days since the year 1900). How do i change this number into a date format that access will recognise?

Secondly, how can i get the formula's to work (taking one date from another, and checking against a third?).. or are formula's strictly for Excel?

Any help would be greatly appreciated!!! I can see how Access will come in handy!

1)What i have learned from the great people here is that you should put an autonumber field in that way you can assign your own Job#

So I think you want to add a row to your table that says "JobID" make this this Autonumber and make it your Primary Key.

Make sure that when you relate it to other tables it has the Foriegn Key

Try and see if some of the posts i have in here help because I have asked the same questions. It looks like we are trying to do the same thing.

2) youll be making a query with Criteria I am not sure what to put in there but thats what i know.

Your date format problem.
This is for the future make sure you put an "Input Mask" in the Properties of that field the "Input Mask Wizard has a couple of selections for both entry and saved format.

scandalous, thanks for your reply!

1) I'd rather use Autonumbers, as it's exactly how our job numbering system works anyway! Problem is, my autonumbers start from 1. Where as our job numbering system (for this purpose anyway) starts at 4595. How can I get the autonumber to start from 4595 without creating 4594 empty records at the beginning?

Also, what's a foreign key?

As for the dates, i'll have a look at teh input mask wizard.. I'm not sure how this'd work though! I'll give it a go, thanks :)

I think you have the same confusion about what the autonumber property is for. I had the same problem. I posted the exact same question in the Access VBA forum and this is what I was told:

===========================
These are called "Regular Expressions" and MSDN has a whole series of articles on them.

Basically what you do is create a public variable (As String) again in a Module.

Then concatenate the values you want in the string you are passing. So the On Click event of the button you use to open the new form would be this:

Dim dtDate As String
Dim sSalesID As String 'it looks like

dtDate = DatePart("d", Date) & DatePart("m", Date) & Right(DatePart("yyyy", Date), 2)

sSalesID = Me.SalesIDFieldName

'Assume Public variable name is pMyString

pMyString = "Q-" & dtDate & sSalesID & ...

I am not sure where you are getting your number sequence from. Where is that coming from? If you are generating it, what is it based on? Number of quotes that day, or number of quotes ever?

Anyway, now that you have the public variable value, close the first form and open the second.

The second should open to a new record. On the Second form's On Load event, put this:

Me.QUoteNumber = pMyString
===========================

You need to know that the autonumber as a primary key is pretty much only used by access to insure that a unique ID is used to relate to other tables.

a Foriegn key is when you place the Primary Key of one table in another that has a different Primary Key to relate the two tables.

I would suggest at looking at all of my recent posts because I think they will help you. Your trying to make the same thing I am with a little twist.

Make sure you look in the Access VBA forum on here too because they have alot of helpful things. If you are trying to do something that you dont know what its called, I found it helpful to describe what your trying to accomplish and ask what its called.