By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

MS Access 2003 - Table Design Advice

P: 21
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 :)
Dec 10 '06 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 10 '06 #2

P: 21
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
Dec 10 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 10 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
Mary,

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

-Ade,
Dec 10 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 11 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 11 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 11 '06 #8

P: 21
Thanks alot Mary, that's helped alot. Have a great evening.
Dec 17 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks alot Mary, that's helped alot. Have a great evening.
You're welcome Atheana

Mary
Dec 17 '06 #10

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 17 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.