Paul:
Queries are at the very heart of relational database applications and can
vary from the very simple to the very complex. The simplest form of query
would simply return all the rows for all columns from one table. A query can
be 'restricted' on certain columns so that only those rows which match a
condition are returned, e.g. only customers in London. It can be 'projected'
over certain columns so only certain columns are returned, e.g. only the
customer name and the order date are returned.
More often than not a query will return rows from more than one table via a
'join' operation. A join can be an INNER JOIN, in which case only rows where
the values in both tables in the columns on which the join is based are
returned, e.g. only those customers who have made orders. It can be an OUTER
JOIN in which all rows form one table are returned but only those from the
other table where values in the join columns match, e.g all customers,
raegardles of whether they've made orders and the order dates for those that
have; the order date column would be Null in those that haven't. OUTER JOINS
can be LEFT or RIGHT depending on which table you want all rows returned from.
Queries can aggregate values by means of operators such as COUNT, SUM, MAX,
MIN, AVG (these are often called 'total' queries in Access). In most cases,
but not all, the data is aggregated over a group, e.g. a query grouped by
CustomerID and return the COUNT of orders by each customer. Grouped queries
can be restricted by a HAVING clause which restricts the results on the basis
of the aggregated values over a group, e.g. the count of all orders for
customers HAVING a count of orders of at least 10.
A query can take a 'parameter'. This is a value which restricts the result
on the basis of a value obtained at runtime, either by prompting the user for
the value or by referencing a control on an open form in which the value is
present.
There are also what Access calls 'action queries'. These change data rather
than returning it, e.g. increase the unit price of all products by 10 per
cent.
A query might have within it a subquery. This is often used to restrict the
'outer query', e.g. it might be used to return all the last order per
customer.
I could go on. Whole books have been written about queries. I hope my
brief and selective outline will have shown you what powerful tools they are
in extracting information out of data.
When you want to return all detailed information and at the same time
aggregated values over a group, which seems to be the case here, you would
need to use subqueries to return the aggregated values in the same query.
This not only makes the query more complex, but more inefficient. Its far
simpler to use the query to return the detailed information by projecting it
over the columns you need and use the facilities in the report to group and
aggregate the values. That's why reports include this functionality. Have a
look at the invoice report in the sample Northwind database for an example.
As regards the form you don't need the totals in the form; the report
computes them from the base data in the tables in its RecordSource query.
What you are doing in the form at present sounds fine to me.
One thing you should never do BTW is include a column in a table to hold
aggregated data derived from other columns. The aggregated values should
always be computed from the data on which the aggregation is based, e.g.
don't have a GrossPrice column in a table made up of NetPrice +
(NetPrice*TaxRate). The gross price can always be derived from the values in
the net price and the tax rate columns in the row of a table, so will always
be correct. If its stored as a value in a separate column one of the three
columns' values could be changed making them inconsistent with each other,
what's known as an update anomaly.
Ken Sheridan
Stafford, England
Paul said:
Dear Ken,
Thank you for that really detailed reply. Can I ask why exactly I
shouldn't do the calculation in the query? I'm just interested to
understand exactly what the purpose of a query is.
Can I try to summarise your suggestion just so that I understand it's
principle:
The query in this case is simply to group information together. It will
pull data from the Invoice and Invoice details tables together. I will
then base my report(s), not on the fields in those tables but on the
fields in the query. The calculations will be done in controls on the
report(s) using data from the query too.
If I have understood your principle, please let me know and I will try
to implement it (and no doubt get back to you when I mess it up!).
One final question - what about forms? I will need these totals to be
displayed as I fill in "Invoice details" data in a form. I guess if I
do the same it wouldn't work in real-time. Perhaps I should, for forms,
do what I did originally (calculate TotalHours in subform using data
from Invoice details table; calculate InvTotal in main form using
HourlyRate from Invoice table and TotalHours).
Ken said:
Paul:
I'd suggest that you use a query for the report's RecordSource, but don't do
the calculations in the query, do them in the report.
The query for the report would join the two tables on the InvNo columns
(fields) and would return the InvNo column from the Invoices table, along
with other relevant columns from that table including the HourlyRate, and the
relevant columns from the Invoice Details table, but not the InvNo from that
table as you already have that from the Invoices table.
Group the report on the InvNo column in the sorting and grouping dialogue in
report design view and give it a group header and footer. Put the fields you
want to show about the invoice as a whole in the group header as text boxes,
and those about the details in the detail section of the report.
To show the cost per item in the report put a text box in the detail section
with a ControlSource of:
=[HourlyRate]*[Hours]
to show the total cost per invoice put a text box in the group footer with a
ControlSource of:
=Sum([HourlyRate]*[Hours])
If the report includes multiple invoices and you want to show a grand total
for them all invoice put a text box in the report footer with a ControlSource
of:
=Sum([HourlyRate]*[Hours])
i.e. the same as in the group footer; in this case it will sum over the
whole report, not the group.
If you want to the report from the form to show the current record add a
button to the parent form with code in its Click event procedure to preview
it:
' first ensure record is saved
RunCommand acCmdSaveRecord
' then open report
DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:= "InvNo = " & Me.InvNo
or to print it:
' first ensure record is saved
RunCommand acCmdSaveRecord
' then open report
DoCmd.OpenReport "YourReportNameGoesHere", _
WhereCondition: = "InvNo = " & Me.InvNo
You might want two buttons, one to preview , one to print it. I've assumed
in the above that InvNo is a number data type ; if its text data type use:
WhereCondition:= "InvNo = """ & Me.InvNo & """"
Ken Sheridan
Stafford, England
:
This could be an incredibly basic question but I'm having trouble
understanding the concept of Queries, even using the Access for Dummies
book! I understand that queries are used to sort and select data from
tables as well as performing actions on it. But I seem to have
understood that I should use a query for simple calculations too.
However I can't seem to make it work and have a feeling that I'm
barking up the wrong tree. Let me explain:
I have (amongst others) two tables:
"Invoices" containing InvNo, InvDate, HourlyRate (amongst other things)
"Invoice details" containing InvNo, ItemDesc, Hours
I have a form for entering "Invoices" information and a subform for
entering "Invoice details" information. I had originally summed up
hours in the footer of the subform and calculated total price with an
equation in the main form, referencing TotalHours in the subform and
HourlyRate in the main form. This worked ok but I then needed to do a
similar thing in a Report. It seemed from what I've read that it would
be sensible to perform these calculations in a query.
Question 1: Should I use a query to perform these calculations?
When referring to a field in a subform as above, I used the Control
Source property with the following expression: =[HourlyRate]*[Items
subform].Form![TotalHours] . This was fine. But although the
calculation in the Query seems fine, I get a #Name? error if I put
[Invoice totals query]![InvoiceTotal] in the Control Source property.
I read that DlookUp would be more appropriate but when I use
=DLookUp("[Invoice totals query]","[InvoiceTotal]") in the control
source, I get a #Error error.
Question 2: How should I refer to a calculated field in a Query?
In both the form and the report I want to display the data relevant to
the current record. It seems as though it was much easier to do this
when the calculations were within the original form! I assume I need to
set up a relationship somehow so that Access can determine which record
in the query refers to which record in the form/report.
Question 3: How do I display the data relevant to the current record
only?
Question 4: Now that I've explained the problem in more detail, please
confirm your answer to question 1!!!
Thanks hugely in advance.
Paul
