MS Access 2003 - Table Design Advice

[antheana]

Hi there,

I am currently redesigning a database, which includes a contracts table that captures information about each contract e.g.

ContractID (PK)
ContractReference
ContractTypeID (FK)
DateCommissioned
JobTitle
StartDate
FinishDate
ContractLength
Rate
RateTypeID (FK)
Quantity (hours/days)
Expenses
ExpensesType (FK)
ClientContactID (FK)
TrainerID (FK)
ContractClient (FK)
VenueID (FK)


The query I have is related to future invoicing. If I have a contract that lasts 6 months, but I am required to invoice for it every week - what would be the best way to design the table? The only thing that may change is the venue and the number of days worked in that period e.g. week. Would it be better to create a new contract for each week/fortnight/month I am due to send an invoice? Or is there another way

I will have a separate table called Invoicing, where I will capture the invoice number, date, calculate the payment due date, sent checkbox, payment terms, calculate the invoicetotal based on the contract length and the days/hours worked per day - if that makes any sense...

help! As you can tell I'm a little confused about the layout so any suggestions would be most appreciated :)

[NeoPa]

This is too complicated a question for a simple answer.
Only you know the whole situation well enough to make the final decision (I'm not going to get into this to the depth I would need to to answer your question for you - that would be too much). Someone else may still though.
I can provide a link (How to structure your tables) and some more general advice :
When you're thinking of table structure, create any table that you think will make life easier for you. Life is made harder when trying to shoehorn data into an innappropriate table structure. Normally if you have to repeat (non-key) data in multiple records that would indicate your table structure could be better.

[antheana]

Thanks for that. I'll have a read and see how I can restructure. I didn't want you to tell me the actual structure, just some advice, so I'll look at how I can simplify it all.

I think my problem is that I am trying to automate too much. I'll have a rethink about what information is absolutely vital and can be generated and what can be inserted manually.

Thanks

[MMcCarthy]

Thanks for that. I'll have a read and see how I can restructure. I didn't want you to tell me the actual structure, just some advice, so I'll look at how I can simplify it all.

I think my problem is that I am trying to automate too much. I'll have a rethink about what information is absolutely vital and can be generated and what can be inserted manually.

Thanks

You will need a separate table to store generated invoices. You will also need a field to store the week/fortnight/month value in your contracts table. I would make this a look up field to avoid errors in user entry.

You will then have to generate your invoices based on a query which examines the values in your contracts table. Automating this would require vba code.

Mary

[NeoPa]

Mary,

Desperately trying to avoid working still then (:D)?

-Ade,

[MMcCarthy]

Mary,

Desperately trying to avoid working still then (:D)?

-Ade,

I was in the middle of trying to correct someone elses code where they had designed the query for the record source of a report in vba. It was long, complicated and kept throwing errors of various types. I got fed up and took a break for a while. Went back and got it working eventually. Now I'm taking a break to celebrate. :D

Mary

[NeoPa]

I was in the middle of trying to correct someone elses code where they had designed the query for the record source of a report in vba. It was long, complicated and kept throwing errors of various types. I got fed up and took a break for a while. Went back and got it working eventually. Now I'm taking a break to celebrate. :D

Mary

Good for you Mary.
I'll catch up with you tomorrow when you've passed the 2,000 barrier ;).

-Ade.

[MMcCarthy]

Good for you Mary.
I'll catch up with you tomorrow when you've passed the 2,000 barrier ;).

-Ade.

I mightn't wait until tomorrow Ade.

Maybe you should stay up all night. :D

Mary

[antheana]

Thanks alot Mary, that's helped alot. Have a great evening.

[MMcCarthy]

Thanks alot Mary, that's helped alot. Have a great evening.

You're welcome Atheana

Mary

[NeoPa]

I think my problem is that I am trying to automate too much. I'll have a rethink about what information is absolutely vital and can be generated and what can be inserted manually.

Thanks

Antheana,

You'd be surprised at how much you can automate in Access. I think the goal should be to automate where possible/practical. We can help you there, I'm sure.