Creating a copy of table data

[handlebars]

I have been trying to design a simple job costing database, to keep track of quoted jobs against actual costs (in terms of time and materials).

What i want to be able to do is to create a new job from a quote form (with all related data). What is the best way to do this? Do I want to do some sort of update query so that once the job number is assigned it will update all records with the related quote number?

Is there a simple way of performing this function?

Any help would be appresiated

Andrew

 

[TnGranny]

Build a Customer table which has customer name, address, contact, phone, email (or whatever information you need about customers that doesn't change from job to job.)

Put "Customer ID" as a non-key field in the Quote table with allowable value of null. On the form where you enter new quotes, require either:
. a valid Customer ID OR
. the static information you need for potential new customers (Company name, contact, phone, etc.)

Put "customer id" (but no other fields already in the Customer Table) and "quote number" fields in the job control table (or main job table whose key is simply job id). Other tables related to the job shouldn't contain quote number, customer id or any information maintained in the Customer Table.

The source of the form where new customers are entered will be the Quote Table. The filter property on this form will be (QuoteTable.CustomerID is null). Show the customer info as entered on the quote table and allow all fields except the Quote number to be edited. Add to the form (as unbound text objects) any fields on the Customer Table which are not on the Quote Table. Also add an option button named "Add Customer" which has the event property OnClick=AddCustomerMacro.

The macro &quot;AddCustomerMacro&quot; will run an append query which adds a record from the Quote table to the Customer Table. The selection on this query is QuoteTable.QuoteNumber = [Forms]![NewCustomerForm]![QuoteNumber]. Appended fields should be based on the values entered on the form (i.e [Forms].[NewCustomerForm]!<form field's name>.

Basic job information (that on the job control table) is entered on a form based on a query which selects quotes that aren't yet jobs. This query's selection is &quot;job number is null&quot; and it's output field(s) are Quote number and anything else necessary to verify that the correct quote number has been selected on the form. The query includes the Quote table joined with
. the Customer Table by Customer id and
. the job control table by Quote Number with join properties set to &quot;include all records from the Quote Table and only those records on the job control table where the joined fields are equal&quot;.
A command button on this form runs a macro &quot;OnClick&quot; that appends the job contol table with the information entered on the new job form. This append query uses the Quote table with selection QuoteTable.QuoteNumber = [Forms]![NewJobForm]![QuoteNumber].

The time and materials table(s) are populated from time tickets entered when work is done, from purchase orders or invoices for materials ordered for a single job, and from
entries for materials taken from inventory.


Note 1: there are more considerations on these forms if you need to add customers or jobs where there is no corresponding record in the Quote table.

Note 2: If the person who enters new clients also enters new jobs, opening the new job form should be added to AddCustomerMacro. Put ([Forms]![NewJobForm]![QuoteNumber]=[Forms]![NewCustomerForm]![QuoteNumber]) as an additional filter on the OpenForm statement in the macro.

Hope this makes sense and helps you get going...

 

[handlebars]

This is really helpful, although do i not need to update all related items with the new job number? Otherwise all quoted items will not be updated with the job number which is needed for getting final budgeted vs actual totals.

Am i missing this somewhere?