How to get data out of database suggestions?

[John Egan]

I have a database setup with various tables and forms to store data for a
small autoelectrical business. I have Three tables for Job information.
JobCard, JobDetails and VehicleDetails. JobCard - one to many - JobDetails.
VehicleDetails - one to many - JobCard.
I have a form setup to enter all data for each job into these tables.
My problem is figuring the best way to get all this data out again to write
an Invoice. Each invoice can have more than one job and I need to link the
Pk in the invoice table to a fk in JobCard.
Any suggestions on how to select one or more Jobcards and print to an
invoice.
The following are the tables

Any help greatly appreciated

John

tblInvoice
pkInvoiceID(autonumber),
InvoiceNumber (incremetal number for invoice).
InvoiceDate
OrderNumber


tblJobCard
pkJobCardID(autonumber)
JobNumber(incremental number for job)
JobDate
JobDetails
TimeOnJob
fkCustomerID(link to pkCustomerDetailID)
fkInvoiceID(link to pkInvoiceID)
fkVehicleDetailID(link to pkVehicleDetailID)


tblJobDetail
pkJobDetailID(autonumber)
Quantity
Discount
fkProductID(link to part used Product table)
fkJobCardID(link to pkJobCardID)

 

[John Nurick]

Hi John,

ISTM you need a link between JobCard and Customer (unless it already
exists via OrderNumber). That way you can have a form bound to the
Invoices table (with a control to select the Customer), with a
continuous subform with a combobox that displays a list of JobCards that
are associated with that Customer but have not previously been invoiced.

I guess you'll also need to set things up to allow for the situation
where of several Jobs on an Invoice the customer pays for some but
disputes others.

 

[John Egan]

Thanks for the advice
There is a link already between JobCard and Customer (fkCustomerID) in
tblJobCard. There is no link directly from tblInvoice to tblCustomer. Can I
still do what you've said from a form bound to tblInvoice.

 

[John Nurick]

You have - presumably - a one-to-many relationship between Customers and
Invoices. This needs to be included in the structure of your data.

I'm not sure about including InvoiceID in the JobCard record, however.
From the implementation angle, it would be easier to add one more table,

tblInvoicesJobCards
fkInvoiceID )
fkJobCardID ) both fields in primary key

Then have the main form bound to the Invoices table and a continous
subform bound to tblInvoicesJobCards, with a combobox whose RowSource is
a query on the JobCards table displaying the needed information about
the job. Linking a job card to an invoice is represented by creating a
record in tblInvoicesJobCards.

Doing it this way also allows the possibility of a many-to-many
relationship between invoices and jobs - for instance if a job can be
invoiced only in part, or if the customer pays part of the invoiced
amount but challenges the rest so a second invoice has to be raised, or
whatever. In that case, you'd add fields to tblInvoicesJobCards to
define the relationship (e.g. "initial invoice").

 

[John Egan]

I don't know why I didn't think of a junction table. Sometimes you can look
at something for so long that you miss the obvious. Thanks for taking the
time to help, that should do exactly what I need.


Regards

John