A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Newbie? Do I use Report or Query



 
 
Thread Tools Display Modes
  #1  
Old June 27th, 2004, 12:44 PM
John Egan
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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.


  #2  
Old June 27th, 2004, 03:06 PM
Traci
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.




  #3  
Old June 27th, 2004, 04:33 PM
John Egan
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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

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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.






  #4  
Old June 27th, 2004, 05:26 PM
Larry Daugherty
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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
--
-Larry-
--

"John Egan" wrote in message
...
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

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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.








  #5  
Old June 27th, 2004, 06:47 PM
PC Datasheet
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"John Egan" wrote in message
...
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

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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.








  #6  
Old June 27th, 2004, 11:30 PM
John Egan
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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

"Larry Daugherty" wrote in message
...
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
--
-Larry-
--

"John Egan" wrote in message
...
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

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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.










  #7  
Old June 27th, 2004, 11:48 PM
John Egan
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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




"PC Datasheet" wrote in message
link.net...
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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"John Egan" wrote in message
...
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

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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.










  #8  
Old June 28th, 2004, 01:56 AM
PC Datasheet
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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

www.pcdatasheet.com



"John Egan" wrote in message
...
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




"PC Datasheet" wrote in message
link.net...
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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"John Egan" wrote in message
...
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
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


"Traci" wrote in message
link.net...
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"John Egan" wrote in message
...
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.












  #9  
Old June 28th, 2004, 05:16 PM
Bruce
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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?
-----Original Message-----
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

www.pcdatasheet.com



"John Egan" wrote in message
...
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




"PC Datasheet" wrote in message
news:_KDDc.30460

...
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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word

Applications

www.pcdatasheet.com

"John Egan" wrote in message
...
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
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


"Traci" wrote in message
news:yvADc.30219

...
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.

--
PC

Datasheet
Your Resource For Help With Access, Excel And

Word Applications



www.pcdatasheet.com


"John Egan" wrote in

message
...
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.












.

  #10  
Old June 28th, 2004, 07:01 PM
John Egan
external usenet poster
 
Posts: n/a
Default Newbie? Do I use Report or Query

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.



"Bruce" wrote in message
...
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?
-----Original Message-----
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

www.pcdatasheet.com



"John Egan" wrote in message
...
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




"PC Datasheet" wrote in message
news:_KDDc.30460

...
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.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word

Applications

www.pcdatasheet.com

"John Egan" wrote in message
...
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
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


"Traci" wrote in message
news:yvADc.30219

...
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.

--
PC

Datasheet
Your Resource For Help With Access, Excel And

Word Applications



www.pcdatasheet.com


"John Egan" wrote in

message
...
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.












.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Form: Print Report Dennis Running & Setting Up Queries 1 June 6th, 2004 01:08 PM
Report Populated by a crosstab query Michael Noblet Setting Up & Running Reports 3 June 1st, 2004 07:11 PM
Newbie: Using Left in query for report? Ed Running & Setting Up Queries 3 May 26th, 2004 11:26 PM
Error running query for report...but query works fine when opened separately John Setting Up & Running Reports 1 May 21st, 2004 04:15 AM
Query opens over report Al Camp New Users 6 May 17th, 2004 04:35 AM


All times are GMT +1. The time now is 02:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.