Re: Newbie? Do I use Report or Query
From: PC Datasheet (spam_at_nospam.spam)
Date: 06/27/04
- Next message: Norbert Lieckfeldt: "Problem with use of calculated form field in query"
- Previous message: Larry Daugherty: "Re: Multi-Select????"
- In reply to: John Egan: "Re: Newbie? Do I use Report or Query"
- Next in thread: John Egan: "Re: Newbie? Do I use Report or Query"
- Reply: John Egan: "Re: Newbie? Do I use Report or Query"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Jun 2004 17:47:38 GMT
<<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
resource@pcdatasheet.com
www.pcdatasheet.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 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" <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 Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> > resource@pcdatasheet.com
> > www.pcdatasheet.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.
> > >
> > >
> >
> >
>
>
- Next message: Norbert Lieckfeldt: "Problem with use of calculated form field in query"
- Previous message: Larry Daugherty: "Re: Multi-Select????"
- In reply to: John Egan: "Re: Newbie? Do I use Report or Query"
- Next in thread: John Egan: "Re: Newbie? Do I use Report or Query"
- Reply: John Egan: "Re: Newbie? Do I use Report or Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
