Newbie? Do I use Report or Query

  • Thread starter Thread starter John Egan
  • Start date Start date
J

John Egan

I have set up a jobcard table and jobdetails table which are linked as one
to many.
The info for time, parts, quantity etc is entered in these tables.

Two questions.
1. I want to collect this data along with prices from a products table and
put it together to print and store as an invoice.
What is the best method, Report or Query or a combination of both.

2. If I manage this, is the info in the Report or Query, such as extended
price, subtotal, total etc, or any other info that I don't want on the
jobcard ok for storage purposes. Should I set up the Report/Query to write
this data to a separate table.
 
John,

From your description, you should have a ProductID field in your jobdetails
table. For data entry you need a main form based on a query based on your
jobcard table and a subform based on a query based on your jobdetails table. The
reason for the two queries is first to put the records in both the main form and
subform in some sort order. A second reason for the query in the subform is to
allow you to create calculated fields in the query. ExtendedPrice would be a
calculated field in the query. In the subform you would use a combobox based on
your Products table to enter the products.

Use a report and a subreport to create invoices. The report would be base on a
query that pulls data from appropriate tables. The subreport would also be based
on a query that pulls the data for each line item on the invoice. It would be
very similar to the data entry subform.

You can find excellent example on how to do all this in the NorthWinds sample
database that came with your MS Office.
 
Thanks Traci
I have a productID field in the jobdetails table. I have spent some time
designing the tables and relationships and I'm fairly happy with them now.
I've looked at the Northwind database and I suppose what I really want is a
conceptual model of how forms, queries and reports work together.
If I open a query in datasheet view, it displays like a table. Does this
mean that a query is as good as a table for data storage.
For data entry you need a main form based on a query based on your
jobcard table and a subform based on a query based on your jobdetails
Click to expand...
table


I'm not quite sure what you mean. I have already setup a form to enter data
into the jobcard and jobdetails tables. This is the first requirement, to
collect the job data. At a later date I will need to invoice for these
jobs. In some instances a single invoice will have details from two or more
jobs. My plan was to setup a query or report to collect and organize the
data from jobcard and jobdetails and Product tables and calculate extended
price, subtotal, tax etc.


From what you say below. Is the query entering the data into the form, or
would I be using the form to retrieve data from the query. Should I scrap
the existing forms and start over.
Any help greatly appreciated

Regards John
 
Hi John,

Most forms and reports are based on queries.

I would never use just a query to display information to an end user.
Always use a report. Using the Report you are able to tailor the display of
your information with headers and footers, pagination. Additional sorting
and grouping functions are also provided in reports.

Dealing with invoices requires more than usual consideration for a first
project. Invoices will reflect the state of affairs at a stated point in
time. Over time, the prices of goods and services will change. If you want
to be able to regenerate an invoice showing the date of the original and the
price extensions as of that date you need to store the current prices of
goods and services, even store the date rather than just getting the current
date. One way around some of the above issues is to create snapshots of the
reports.

HTH
 
<<what I really want is a conceptual model of how forms, queries and reports
work together.>>

The ONLY place data is stored in a database is in tables. Queries are used to
retrieve the data you want from the table(s). Queries DO NOT store data. Forms
are used to enter data into tables and to display the data you want on the
screen. Forms DO NOT store data. Reports are the counterpart of Forms. Reports
are used to give you a hardcopy of the data you want. Reports DO NOT store data.
You CAN NOT use reports to enter data into tables. Both forms and reports can be
directly based on a table or a query. If based on a query, the query can include
one table, multiple tables, one other query, multiple other queries or a
combination of table and queries. When a query includes multiple objects, these
objects 99.99% of the time must be joined together on common fields; thus the
need for primary and foreign keys.

<< If I open a query in datasheet view, it displays like a table >>
This is true! The datasheetview is only for you as the database designer to be
able to review the data the query returns to be sure it is what you want. A
query SHOULD NEVER be used in the final database to display data. As stated
above, a form or report should be used to display data. As Larry said, forms and
reports are usually based on a query.

<< I'm not quite sure what you mean. I have already setup a form to enter data
into the jobcard and jobdetails tables. >>
If you're not sure, you probably don't have the correct forms and it is highly
likely that the design of your tables are incorrect. I suggest you post your
Product, JobCard and JobDetails tables showing the fields in each for further
comment.

Before we deal with your Invoice, let's be sure your tables are correct.
 
Hi Larry
Thanks for the reply. The problem you mention about getting a
freeze-frame of data in each invoice as it is generated is exactly what I am
having difficulty with. I need to go back to any invoice and have all the
fields, including generated fields available. How do I create snapshots?

John
 
Thanks for the information, I'm gradually getting a picture of how things
work after setting up the tables.

The tables are setup as follows

Table Jobcard
JobNumber PK(autonumber)
Date
CustomerCode
VehicleMake
VehicleRegistration
JobDetails
JobCode
TimeOnJob

Table JobDetails
JobNumber (Dual PK for JobNumber and ProductCode)
ProductCode
Description
Quantity


Table Products
PK ProductCode (suppliers part number)
ProductName
Supplier
UnitPrice
UnitsInStock

Relationships are JobNumber-JobNumber and ProductCode-ProductCode.

I also have tables for Customers, Suppliers, JobCode, PaymentsIn and
PaymentsOut.

Any constructive critism or suggestions for improvement greatly appreciated.

Regards John
 
1. As a suggestion, end your primary key fields with "ID" - this easily
identifies a field name as a key. Makes reading your database easier!
2. As a suggestion, name your foreign keys the same as the corresponding
primary key - identifies a field as a foreign key. Makes reading your database
easier!
3. In Table Jobcard, change the field named "Date" to a different name - "Date"
is a reserved word in Access
4. Assuming a supplier provides more than one product, you need a suppliers
table and then refer to the supplier in Table Products by SupplierID
5. Combine your PaymentsIn and PaymentsOut tables into one table named
TblTransactions and identify the type of transaction with a field called
TransactionType which would have the value "In" or "Out". You can then easily
get a balance by just adding up all the values in the table.
6. You need a foreign key, "JobnumberID", in Table Job Details that relates
that table to Table Jobcard.
7. What is "Description" in Table Job Details? If it is for the product - you
don't need this field.

I suggest the following tables:
Table Jobcard
JobNumberID PK(autonumber)
JobDate
CustomerID
VehicleMake
VehicleRegistration
JobDetails
JobCode
TimeOnJob

Table JobDetails
JobDetailID
JobNumberID
ProductID
Description
Quantity


Table Products
PK ProductID
ProductCode (suppliers part number)
ProductName
SupplierID
UnitPrice
UnitsInStock


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
I really like the way you are explaining things in this
thread, but I am puzzled by something. I'm not sure I
follow why Job Details would be a field in the Job Number
table. Having the PK from Job Number be the FK for job
details would accomplish what is needed, I would think.
Or am I on a different page?
 
Thank you for your suggestions, they all make sense. Just two questions

1. Same question as the next post. Why have three PK fields in JobDetails.

2. I have read conflicting opinions on this. Is it ok to use the table
autonumber as both table ID/PK and as an identification number for that
record outside the database. For example would using JobNumberID as the
jobcard number cause any problems.
 
I would not use autonumber PK as anything other than PK.
It would never be visible except for design purposes.
Having said that, I have used Employee ID numbers as the
PK in an Employees table, but I would probably stay away
from that were I to do it over. I would be inclined to
use for the job number a number that increments by one (or
ten or whatever you want) over the previous job number.
If autonumber is used, starting a record then deleting it
(to use one example) would result in a gap in the job
numbers. Later it might be difficult to know why the gap
is there. Was the record lost? What happened? Why is
there no record of it? Short answer to question 2: You
can, but you really don't want to.
-----Original Message-----
Thank you for your suggestions, they all make sense. Just two questions

1. Same question as the next post. Why have three PK fields in JobDetails.

2. I have read conflicting opinions on this. Is it ok to use the table
autonumber as both table ID/PK and as an identification number for that
record outside the database. For example would using JobNumberID as the
jobcard number cause any problems.



or
a combination of Report
or Query, such as
Click to expand...


.
[/QUOTE]
 
I would not use autonumber PK as anything other than PK.
It would never be visible except for design purposes.
Having said that, I have used Employee ID numbers as the
PK in an Employees table, but I would probably stay away
from that were I to do it over. I would be inclined to
use for the job number a number that increments by one (or
ten or whatever you want) over the previous job number.
If autonumber is used, starting a record then deleting it
(to use one example) would result in a gap in the job
numbers. Later it might be difficult to know why the gap
is there. Was the record lost? What happened? Why is
there no record of it? Short answer to question 2: You
can, but you really don't want to.
-----Original Message-----
Thank you for your suggestions, they all make sense. Just two questions

1. Same question as the next post. Why have three PK fields in JobDetails.

2. I have read conflicting opinions on this. Is it ok to use the table
autonumber as both table ID/PK and as an identification number for that
record outside the database. For example would using JobNumberID as the
jobcard number cause any problems.



or
a combination of Report
or Query, such as
Click to expand...


.
[/QUOTE]
 
Back
Top