Newbie? Do I use Report or Query

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.



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












.



.

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.



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












.



.