Making an interactive database in MSAccess Xp

[merdok]

Hi, I'm trying to build a database in Access XP. I currently have four tables:

Job Sheet
Small Jobs
Customers
Staff

The job sheet is a log of all jobs taken, it has all the details in it required ie customer name, date taken, type of job, amout paid and due. There is also a job number which must be a primary key starting at a specific four digit number,( here we come to my first problem,i dont know how to start an autonumber sequence at anything other than zero.)

Once the job sheet has been filled in it must update the customers table (ie either add a customer to the list or add the job details to an existing customer record) - this is my second problem, I have no idea how to do this either.

It appears that I have bitten off much more than I can chew, however I have promised that I would do this now, I used to be able to use access quite well but I dont remember it being this hard, if ANYONE can help with either of those problems I would be very happy!

[OBonio]

http://techrepublic.com.com/5208-6239-0.html?forumID=44& amp;threadID=140424

Personally, I'd use another table called AutoNumbers with a tablename field and a longint field and produce the numbers yourself (ie, increment the number yourself each time you add a record). Maximum flexibility.

You'll need a CustomersJobSheets table to join the Customers and JobSheet tables in a one to many fashion (ie, there can be more than one jobsheet assigned to one customer). This would hold the id of the customer and the id of the jobsheet.

When adding a new jobsheet, you need to do a lookup on Customers (SELECT CustomerID FROM Customers WHERE CustomerName='MyCompany'), if the result is NULL, create a new record, if it's not, update the record.

It's been a while since I've used Access myself, more of SQL Server / Oracle bod nowadays but I think the same logic applies.

[tomd]

Why can't you just use a one-to-many relationship without the connecting table? One customer can have many jobs, and many jobs can be connected to one customer with just the two tables.

[rinkytink]

 

Why not use the service call management template that comes standard with Access 2002 ? You can then customise this - although it looks like it already has many of the things your are looking for.

[OBonio]

tomd is right, sorry. You just need a CustomerID field in the JobSheet table. What I was describing was a many to many relationship solution (ie a customer can have many jobsheets and a job sheet can be assigned to many customers).

[merdok]

Brilliant! That makes sense... cheers guys, I'll have a go tonight.