Total DB Newbie stuck HELP please!

[June7]

You have a tblProjectHoursInvoiceList where you appear to be saving a Total. Aggregate date is normally not saved, it is calculated from transaction records when needed. Same goes for InvoiceTotal field in tblInvoice.

And same for ProjectActual field in tblProject.

Not sure what InvoiceDetails field is for.



Might want to revisit the tblInvoiceDetailTable Steve showed in his example.

Quantity field in tblProjectProductsInvoiceList needs to be number type. Only save product Price here if there is a chance it could be changed in tblProduct in the future.

I don't think tblProject needs to link directly to tblInvoice in Relationships builder nor the Project_ID field in tblInvoice.

Advise not to build lookups in table. Build comboboxes and listboxes on forms.

See one way to build invoice in attached db.

WELLCDB003.zip

[CJ_London]

I suggest take out the invoice table for now, it is a distraction.

The products/materials used needs to be connected to the project in some way - perhaps via the project hours table on the basis that an employee is spending time 'doing something' with 'something'. It doesn't matter if they are doing something that does not require materials - just leave the product FK field blank

Once you have that working and reporting correctly, add your invoice tables back in

Other considerations, prices and rates change. These should be in a separate table with an 'effective from' date.

I would also consider the you might need a a product type (switch/socket/2.5mm T&E, etc) table with a vendor productcode/description in a separate table. But you might be able to use a lookup from the product table.

And just a thought - cabling can be bought in a variety of lengths, wholesale boxes of switches might come in 20's or 50's etc. so you might just need to cater for this requirement as well - particularly if you only use 10m of cable from a 50m roll.

[orange]

I agree that that data model from Barry Williams' site is generally what you described. The devil is in the details -as always.
His models are a great starting point and can often be adjusted(added to/parts removed) to tailor to your needs.

Did you work through a tutorial from RogersAccessLibrary as mentioned in post #9?

Sadly Tutorials about queries and forms all seem to be the same if all i needed to do was query 1 page to find record with the highest this or that match a certain that I would be ok, but how to pulll the project hours and parts used into a nice looking form to use to bill the customer.... that's where i get lost.

You need some sample data values in your evolving tables (again this can be pencil and paper) and a description of what you want to see/report. If the data you need is in the "evolving design" or "can be derived from data in the evolving design", then there is strong probability it can be extracted into the output you need. It is critical to get your tables and relationships designed to meet your needs.

Have you mocked up a set of the various outputs(report/form) you expect from the data?

Most, if not all, projects can seem insurmountable at the start. But as you start a systematic attack, to identify the "business facts" and the relationship between such facts, some of the outputs which help ensure that required inputs have been identified....
It's like the old How do you eat an elephant? 1 piece at a time. Keep that in mind as you move forward.
Good luck.

[DBID10T]

Morning Everyone,

Orange, I worked thru the Widgets and caterit but both of those exercises leave you with a proper data model but no forms and reports. I have mocked up a form on paper for a project invoice interestingly enough It's almost exactly what June7 whipped up. Basically a Printable Invoice that lists CustomerName CompanyName and the Date, then a list of hours and parts used and a total.

Ajax, Hey thanks for the reply, I see what your saying about the invoice table being a distraction and to remove it, the old excel user in me wants a table wiith all that info in it, but as I am learning Access does not work that way, it's all about queries and reports. I Like the idea of Categorizing Parts by Type I will have to implement.

June7, HOLY HELL THAT'S IT............now to figure out your dark wizardry..........More seriously yes that rptInvoice is almost exactly my hope for the end result of this process. But how to build that is where i am loosing my mind.

[June7]

The report I built is really quite basic. Any introductory tutorial book should have enough guidance to replicate it. However, simply open it in Design view and start exploring.

[DBID10T]

The report I built is really quite basic. Any introductory tutorial book should have enough guidance to replicate it. However, simply open it in Design view and start exploring.

Good to know that I just need to learn it then. I went all the way back to square one and redesigned from square one anyone have any advice or critical feedback?

Alright next step, Learn to talk to the Database.
Thanks Everyone this has been a really rewarding process, even more so if I get it to actually work as i intend.

[CJ_London]

couple of things on your latest design - although perhaps you just have the 'hooks' there to deal with later

Nothing there I can see about product prices, and labour rates do not appear to be transferred to the invoice - so how do you calculate the invoice value?

If this is a commercial operation then depending on where are, you are no doubt need to account for VAT, Sales Tax or equivalent so you need to account for that in your invoicing?

[June7]

If you look at table structure in my sample, you will see that ProjectID_FK is not in tblInvoice.

When you build queries, Access will use established Relationships to build links in query. There is more than one path to get to tblInvoice. You could end up with multiple links in query involving ProjectID that causes confusion to Access as well as anyone reading the query. These query links can be manually edited to simplify query. You have to decide which path to use. My structure did not have this issue and I was able to produce the example invoice report. Might find this of interest https://www.codeproject.com/articles...atabase-design

[DBID10T]

couple of things on your latest design - although perhaps you just have the 'hooks' there to deal with later

Nothing there I can see about product prices, and labour rates do not appear to be transferred to the invoice - so how do you calculate the invoice value?

If this is a commercial operation then depending on where are, you are no doubt need to account for VAT, Sales Tax or equivalent so you need to account for that in your invoicing?

Thanks for your help, I thought the addition of tblProjectLineItem basically being the nexus where Hours,Products, and Permits of a project are tracked and cataloged then sent to an invoice form when created. Funny you mention the taxes, that's actually what started this whole process rolling in my head. I have to Download a quarterly list with the Combined Sales tax rate for every city in the state, and do the math myself. The State has a downloadable CSV file every quarter that can be integrated later I hope to make Tax by city an automatic process.

I really thought Retail Price and Quantity of hours could be recalled thru the PK-FK relationships, I just need to learn how to make the forms and hopefully by the time I get that down adding in automatic sales tax will be easy peasy.

[June7]

I really thought Retail Price and Quantity of hours could be recalled thru the PK-FK relationships,

Isn't that what I demonstrate in sample invoice?

[orange]

DBID10T,

Yes, I was aware that the RogersAccesLibrary tutorials would take you from a business description to a set of normalized tables and relationships-- a blueprint for your database. And further, that working with this model (paper and pencil) you could determine if stored data was sufficient for the reports/invoices/forms etc that you required. The underlying structure is key to a successful database. I hope the design experience from the RogersAccessLibrary tutorials was useful for you.

You can find sample reports, invoices, bill of lading...... using google. Very helpful in design and for mock-up/testing.

It appears that your basic concern now is access queries and other Access objects. There are several articles and youtube videos
mentioned in the Database Planning and Design link. Richard Rost and Steve Bishop have tutorials at youtube, and there are others.

Continued good luck with your project.

[CJ_London]

r

eally thought Retail Price and Quantity of hours could be recalled thru the PK-FK relationships,

it can, but a bad idea. So today the price is $100. You generate the invoice and send to your customer. tomorrow the price changes to $90. Next week your customer calls to say the dogs eaten the invoice, can you send another one. What's the invoice going to say?

[DBID10T]

June7

Oh shoot! good catch my mistake ProjectID_FK was supposed to go to the tblProjectLineItems table not the invoice table, thanks. Fixed.

DB Uploaded DBDesign4.zip

Just starting on some test data and trying following a few forms tutorials

[DBID10T]

[COLOR=#333333] rit can, but a bad idea. So today the price is $100. You generate the invoice and send to your customer. tomorrow the price changes to $90. Next week your customer calls to say the dogs eaten the invoice, can you send another one. What's the invoice going to say?

Excellent Point, I kinda figured I would be filing hardcopies of anything I send but I like the idea of being able to recal what it was sold for in each instance, perhaps I can add LineItemTotal with every associated entry on tblProjectLineItem?

[orange]

Further to Ajax's comment on retailPrice, Quantity....
see this link on AgreedToPrice for more info and context.