How to limit multiple records against a unique Job# (summary)?

[zkrucz]

@ ssnafu,



This is great. You are more than helpful!

Yes, the business model revolves around field techs using tablets. tblTable1 is used to gather all quotes and the generation of JobNum's. tblTable2 is used to collect all Work Orders and tblTable3 is used to collect time sheet info. Yes, if the personnel structure changes, then that part would need to be revised with all the Access work that goes with it. And, if different equipment is purchased or decapitalized, more changes. But, typically these businesses are pretty stable/consistent over time in both cases so, I don't expect too many changes once set up.

As you can see from the form structure, the biggest focus/concern is to be able quickly determine profitability and to be able to invoice quickly. Timely invoicing is always the biggest issue for contractors as they really have no time to deal with it. And, figuring out profitability is usually non-existent, especially in a timely manner. So, the Quotation part actually prepares at the time of Quotation (on the tablet) the proposed cost and quote structure, and once the Work Orders come in, then the contractor knows immediately his profit/loss per JobNum and/or per Account.

Again, thank you for all your help!

[ssanfu]

OK, a few more comments, then I'll leave you alone. Here are my thoughts..........

I missed a field - in tblAccounts, there is a field named "Inv_House#" that I should have changed to "Inv_HouseNum".

Regarding the "duplicate" fields between Table1 and Table2, they are not really duplicates. Both tables are populated by information coming from our techs using iPads. Table1 reflects quotations. A lot of that information from Table1 is then dispatched to those iPads and then sent back (with additional info) and populates Table2, as work orders (and there may be many work orders against a unique JobNo).

You are using iPads???? How does that work? Using RDP or a VPN??

Anyway, attached is the dB (mod3) and an Excel worksheet with a few comments.

The Invoice fields in tblAccounts should be in their own table. Each account can have multiple invoices.... Right?

I merged three tables (tblEQUIP_ID, tblLABOR_ID and tblMAT_ID) into one table "tblResources". (And I hid the 3 tables: tblEQUIP_ID, tblLABOR_ID and tblMAT_ID)

The Excel sheet shows the duplicate fields in Table1 and Table2.
I would have separate tables for Labor, Equip and Materials.

tblTable1 is used to gather all quotes and the generation of JobNum's. tblTable2 is used to collect all Work Orders and tblTable3 is used to collect time sheet info

After looking at the relationships a while longer, why is tblTable3 (Time Sheet Info) Linked to tblTable1 instead of tblTable2?You don't care/track costs by WO, only by JobNum?

So if I understand correctly
Each Account can have multiple Quotes (which are converted to Job Numbers)
and each Job can have multiple WO
that can have multiple Equip requests, multiple materials and multiple labor costs??

Which brings me to costs. You have a trailer cost of 200. Is that per job, daily cost, hourly cost? There is no field in a table to record usage.
If a job requires 3 trailers, is the cost still 200? If 20 ladders are required, is the cost 5 (dollars)? Or 100? Or 100 per day?


Last comment. Instead of using aggregate functions, I would probably write some UDFs to get the amount. You have much better control over the calculations.



Edit:
PS........ while I'm sure I make my fair share of misteaks , it is ssANfu, not ssNAfu.......

[zkrucz]

@ssanfu,

Thank you! You were more than helpful and I have learned a lot. Yes, should the equipment mix change and/or the personnel organization, a lot of Access work would have to take place to address those changes. However, these small contractors are pretty stable in their organization, so once set up, I do not expect a lot of changes.

All of the input comes directly from the field. Quotations/Jobs are set up via tablets in the field and uploaded into Table1. This includes job cost estimates which are then compared against future work orders which are uploaded into Table2. Table3 is used to keep track of personnel hours for payroll purposes.

As you can see from the set-up, the big focus is on understanding profitability of each job and account. Followed by prompt invoicing. Typically, they are so busy that they have little time for both which of course impacts negatively their business model.

[zkrucz]

@ssanfu,

Thanks for that final correction. You must admit that at least I made a consistent error.

Yes, iPads are used. We use a service that allows our Techs to complete forms (created in the service provider system) which allow for the completed form to be sent as an attachment. The attachment is then process by our software to upload the contents into each Table.

The labor, equipment and materials are standard for all (unless of course the business model changes, which very seldom if at all). Each Work Order can pick from a pallet of standardized list and the Tech needs to fill in Quantity and Hours. Any changes in rates can be updated periodically, as needed. If updated, the rates reside on the provider's servers and are downloaded to each iPad automatically as the provider form is opened up. In other words, rates are always synced up in real time.

Hopefully this sheds more light on both the service that we provide and our customer base and their operation model.

[zkrucz]

@ssanfu,

One last comment. We do need separate tables as our software specifically looks for Table1, Table2, Table3, etc. to upload the incoming data.

[zkrucz]

@ssanfu,

I spoke too soon. I ran the expression that you kindly provided, but I see that it still returns 3 records where I would like to see only one summary of both the Job Costs and the Account Costs. And, it looks like my aggregate sums actually aggregate the 3 records in a way that everything is actually tripled.

So, you mentioned UDFs. Can you provide an example of one (say, for the Total Labor Cost for each job and then for Each Account) which I can use as a template to write the others? I am making the assumption that I can use the UDFs to refer to these summary costs in my Form.

[ssanfu]

From Post #18

Thank you! You were more than helpful and I have learned a lot. Yes, should the equipment mix change and/or the personnel organization, a lot of Access work would have to take place to address those changes. However, these small contractors are pretty stable in their organization, so once set up, I do not expect a lot of changes.

The point is, if the design is correct from the beginning, you won't have to do "a lot of Access work would have to take place to address those changes".

.......All of the input comes directly from the field. Quotations/Jobs are set up via tablets in the field and uploaded into Table1..........

Post #19

......We use a service that allows our Techs to complete forms (created in the service provider system) which allow for the completed form to be sent as an attachment. The attachment is then process by our software to upload the contents into each Table........

So you use 3rd party to have a program/web app on an iPad to create a file - text or CSV?

Sounds like you don't actually do the tree work - maybe you are a billing service?


Post # 21

......I ran the expression that you kindly provided, but I see that it still returns 3 records where I would like to see only one summary of both the Job Costs and the Account Costs. And, it looks like my aggregate sums actually aggregate the 3 records in a way that everything is actually tripled.....

I just was working on the table designs and relationships. As Ajax said, you will have to change the query to get the results you want.


This design is an Excel design, not an Access table.
An Access Table would have to following fields (among others) in separate records.
TotalQuote
LABORCOSTTABLE1
QUOTATIONCOST
EQUIPMENTCOST
MATERIALS_COST_TABLE1
TOTALJOB_COST_TABLE1

[zkrucz]

@ssanfu,

To answer your question, our service is an amalgam of iPad-to-Access DB information accessible by these small contractors. The tree service is an example of what they do. I am stuck with the Excel design because all information comes from iPads which use the service provider's Excel/Word model. As the information comes in via Outlook, the attachments are "deconstructed" and loaded into Access DB, and the information follows the structure of the service provider's form design.

So, I have what I have and I am looking for ways to work around it to get summary values for multiple records. Would I need a separate query to summarize these multiple records first, and then bring the sums into the form?