Normalization

[Seph]

Good day good people of the internet.

I have a database that I use to keep track of jobs, invoices and quotes.

I also have to keep track of travel, stock and labour data linked to each of the above.

Below is my current database format:

When a record in the JobsT table has a corresponding record in TravelT, their linked via their JobsID field. Same applies for Quotes "QuoteID" and Invoices "InvoiceID".

It is working fine. But my issue comes in when I do financial query calculations.

Is this the correct flow or should I redo it to something like this:

 

[GaP42]

Without knowing what the financial calculation / report you are having difficulty with it is not really possible to advise. I do not think the second arrangement is needed, and it should not be regarded as representing a flow but potentially a way to gather required data. While the structure may be working for you seem to have sprinkled identifiers for job and quotes and invoices liberally. So it is difficult to really follow a proper path for the needed relationships. Job Details for instance should not have Quote ID. The Job Details are related to Jobs only. (Is JobID and QuoteID a composite key?) Is Travel related directly to Job and Quote, as well as Invoice? or should all travel be associated with a job, and the relationships to Invoice and Quote arise only from their relationship to Job?
You are presumably trying to produce a statement of account using invoice data for a customer, the quote provided and the job / job details and travel details. What do you expect your report / calculation to look like, as in what elements need to be used and displayed. is there a sequence/order to show?

 

[Seph]

Without knowing what the financial calculation / report you are having difficulty with it is not really possible to advise. I do not think the second arrangement is needed, and it should not be regarded as representing a flow but potentially a way to gather required data. While the structure may be working for you seem to have sprinkled identifiers for job and quotes and invoices liberally. So it is difficult to really follow a proper path for the needed relationships. Job Details for instance should not have Quote ID. The Job Details are related to Jobs only. (Is JobID and QuoteID a composite key?) Is Travel related directly to Job and Quote, as well as Invoice? or should all travel be associated with a job, and the relationships to Invoice and Quote arise only from their relationship to Job?
You are presumably trying to produce a statement of account using invoice data for a customer, the quote provided and the job / job details and travel details. What do you expect your report / calculation to look like, as in what elements need to be used and displayed. is there a sequence/order to show?

Hi there, Thanks for you're feedback. I'll break the replies down in stages:


Without knowing what the financial calculation / report you are having difficulty with it is not really possible to advise.
The InvoiceT table stores the Invoice's final amount. I created SalesQ queries to calculate (via Dsum) StockT & TravelT cost prices of records matching the primary key's of the relational tables (E.g. QuoteID). Its just that the Query is getting large because it must match each Invoice to its relevant primary key record of various tables (QuoteID, JobsID, InvoiceID). Then (Nz) null the record if none or show the calculated data if any. Ultimately subtracting all records found from the InvoiceAmount field to give the Profit total.

While the structure may be working for you seem to have sprinkled identifiers for job and quotes and invoices liberally. So it is difficult to really follow a proper path for the needed relationships. Job Details for instance should not have Quote ID. I did this because I wanted a Quote to eventually be accepted and then associated to a corresponding JobsT record.

The Job Details are related to Jobs only. (Is JobID and QuoteID a composite key?) Their not.

Is Travel related directly to Job and Quote, as well as Invoice?Correct. All 3 have the ability to create relational records.

or should all travel be associated with a job, and the relationships to Invoice and Quote arise only from their relationship to Job?
In terms of Jobs and Quotes for the TravelT, the same principle applies as the JobsDetailsT. As for Invoices. I can create Sales Invoices that are not Jobs related and then need to create entries unique to individual invoices.

You are presumably trying to produce a statement of account using invoice data for a customer, the quote provided and the job / job details and travel details. What do you expect your report / calculation to look like, as in what elements need to be used and displayed. is there a sequence/order to show? The reporting portion is working, because each record is relational to their respective tables. Where I'm getting grey hairs is for my own financial reporting. Like a Profit report. I must/have created queries to filter through the whole database to get the desired outcome.

I know this is a mouth full and am not looking for a easy way out and have someone just give me "The Fix". I'd just like advice on how to structure it so my queries can be a little more smooth.

Thanks!

 

[Pat Hartman]

I created SalesQ queries to calculate (via Dsum)

Never (well almost never) use domain functions in queries or VBA loops. Each domain function runs an independent query so if you have 10 items in the travel table, the domain function will run 10 times. The more rows you have, the longer the outer query will take.

One issue I have with the original schema is that I think the relationship between job and quote is backwards. The structure as it exists does not allow you to create multiple quotes for the same job. This might be important for future analysis to help you identify successful quotes.

To calculate the invoice efficiently, you would use a union query with three embedded totals queries. Each query sums for JobID and QuoteID the amounts from one of three tables - Details, Stock, and Travel.

 

[GaP42]

I note from your reply showing some records in a Job Details, that you can have a job detail record without a job identified. This does look suspicious, like having an invoice detail (line item) without an invoice. You may have a process to "fix" these later so all jobDetails, when completed must be associated to a Job (even if the Job only has one JobDetail). Avoid orphans

If you want a style profit / loss style query then
1. For a job, your paid invoices constitute the income received
2. For a job, stock used gives you qty used/cost price for material expenses
3. For a job, job details gives you time spent, and discount amount, the work expenses
4. For a job, travel gives you travel cost?

Construct queries for each, that are of the same structure that can be combined : union query, to produce the income and expenses for a job.

Where you have some income from the sale of stock which are not linked to jobs? then identify these and construct the needed query to get the charge to the customer (accepted Quote/ Quote Line item) and the cost to you. These are a separate stream of income/expenditure.

For other items, such as Travel that may be a non-job item you also require a separate query to identify these, and determine a customer charge/expenditure. Presumably from accepted quotes? Or Quote line Item?

If these represent all your streams of income/expenditure, then construct a query combining each of these into a consistent format for display/ aggregation.

 

[Seph]

Thank you for taking the time and effort to respond