Re: Newbie? Do I use Report or Query

From: PC Data*** (nospam_at_nospam.spam)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 00:56:46 GMT

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 Data***
Your Resource For Help With Access, Excel And Word Applications
                              resource@pcdata***.com
                                 www.pcdata***.com
"John Egan" <bravo6@gofree.indigo.ie> wrote in message
news:LbIDc.3322$Z14.4451@news.indigo.ie...
> 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 Data***" <spam@nospam.spam> wrote in message
> news:_KDDc.30460$Y3.28334@newsread2.news.atl.earthlink.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 data*** 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 Data***
> > Your Resource For Help With Access, Excel And Word Applications
> >                               resource@pcdata***.com
> >                                  www.pcdata***.com
> >
> > "John Egan" <bravo6@gofree.indigo.ie> wrote in message
> > news:JPBDc.3292$Z14.4212@news.indigo.ie...
> > > 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 data*** 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" <tminetti@earthlink.net> wrote in message
> > > news:yvADc.30219$Y3.14549@newsread2.news.atl.earthlink.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 Data***
> > > > Your Resource For Help With Access, Excel And Word Applications
> > > >                               resource@pcdata***.com
> > > >                                  www.pcdata***.com
> > > >
> > > >
> > > > "John Egan" <bravo6@gofree.indigo.ie> wrote in message
> > > > news:%ryDc.3287$Z14.3882@news.indigo.ie...
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>