Matthew:
I'm not sure about thoughtful; rambling and incoherent might be a better
description <G>. Whatever, I hope it makes some sense and is helpful.
To judge by your web page I think we may be in broadly similar lines of work
(or have been in my case as I'm retired now). I was a Principal Planning
Officer with in the Environmental Planning Unit of Staffordshire County
Council here:
http://www.staffordshire.gov.uk/environment
Back to business:
First start with a simple aggregating query which returns the lowest price
per item per project:
SELECT tblProjects.projectid, itemnamedescription,
MIN(unitprice) AS lowestprice
FROM tblProjects INNER JOIN tblItems
ON tblProjects.projectid = tblItems.projectid
WHERE biddate >= DATEADD("m", -13, DATE())
GROUP BY tblProjects.projectid, itemnamedescription
Save this as qryProjectItemMinCosts say. Then create another query which
averages the costs per item from the above:
SELECT itemnamedescription,
AVG(lowestprice) AS avglowestprice
FROM qryProjectItemMinCosts
GROUP BY itemnamedescription;
Both of the above can easily be created in design view. For the first
select Totals from the View menu, group by tblProjects.projectid and
itemnamedescription, and select Min for the unitprice column. Access will
give the computed column a name like MinOfunitprice, which you can either
keep or change that to lowestprice. The second is based on the first query
and is done in much the same way, grouping by itemnamedescription and select
Avg for the lowest price column (or for the default MinOfunitprice column if
you haven't changed its name in the first query.
If you wish you can then join the second query above to any other query (or
a table) which returns other data about items, joining them on the
itemnamedescription columns. You can then include the columns from the query
to which you join the second query above, along with the avglowestprice
column from the second query above in the result table of the final query.
Bear in mind, though that if one query returned rows for all items and the
other excludes some then you'd need to use a LEFT OUTER JOIN to include all
items. Say for instance you want to list all items and the average lowest
cost for each over the last 13 months, but not all items were included in
projects over that period then you could join the tblItems table to the
second query above, which I'll call qryProjectItemAvgMinCosts, using a LEFT
JOIN (the OUTER is optional and usually omitted) like so:
SELECT DISTINCT tblItems.itemnamedescription, avglowestprice
FROM tblItems LEFT JOIN qryProjectItemAvgMinCosts
ON tblItems.itemnamedescription =
qryProjectItemAvgMinCosts.itemnamedescription;
Note the use of the DISTINCT option here. This is because tblItems has
multiple rows per item so without selecting distinct rows the query would
return duplicate rows for each item. Any items not represented in the last
13 months will have a Null avglowestprice column. Again this query can
easily be created in design view.
BTW from a design point of view you should really have another table with
just the one itemnamedescription column and with each item entered as one
row. This can then be related to your tblItems table with referential
integrity enforced, thus preventing any invalid itemnamedescription names
being entered in tblItems. With this extra table tblItems in fact models a
many-to-many relationship between items and projects. It would actually be
better to call the new table tblItems and the current one tblProjectitems.
Ken Sheridan
Stafford, England
:
You have no idea how nice it is to get a thoughtful response (or maybe you do
Thank you very much Ken!
Yes, I will continue to attempt to learn as much as I can about SQL, as I
would like to enjoy this powerful tool. However, it can be a bit scary, as
you noted.
I believe you are exactly right, in the abstract or not, regarding the
smaller grouping linked together. Although, I'm not sure how to link them
together. And your example is almost perfect albeit hypothetical. Here is
what I mean by 'the average of the lowest priced item per project over the
last 13 months'.
Proj #1: 01/12/2008
Price A | Price B | Price C
Item1 $7.00 | $8.00 | $10.00
Proj #2: 08/23/2007
Price A | Price D | Price E
Item1 $7.00 | $6.00 | $5.00
Take the lowest prices for Item 1 within the last 13 months of projects
(ie-$7.00, $5.00). Then average the lowest values (7 + 5 = 12, 12/2 = $6.00)
The average of the lowest price for Item 1 = $6.00
I will continue to attempt what you described previously as links in the
chain and produce the necessary 'sets'. However I am uncertain as to how I
will link them together.
Best Regards,
-Matthew
--
http://njgin.aclink.org
:
Matthew:
Switching to design view in itself won't produce any errors, but it will
present things in a way where it can be difficult to perceive the logic which
is apparent in SQL and therefore make it all too easy to change the query so
that it does produce an error inadvertently. If you want to create complex
queries using subqueries it is really necessary to learn SQL. It might look
a little scary at first, but its actually quite straightforward and logical
once you get your teeth around it. The hard part is getting a mental picture
of the logical operations involved in squirreling the information you want
out of the data, but that's something you have to do however you design a
query..
I'm having difficulty grasping what you mean by 'lowest price for each item
per project averaged over the last 13 months'. There are two aggregation
operations involved here so this means there are two sets involved.
Consequently there has to be a set of a set, i.e. there must be multiple sets
of values from which a single set of lowest values is derived, from which in
turn an average value is derived. In fact as the end result also seems to be
a set of average values, being the averages for 'each' item', it sounds as
though the end result is in fact a set of sets of sets. I think you'd have
to provide an example, as I'm just not clear in my mind what sets we are
dealing with.
Although I'm not clear on the specifics here, I can nevertheless answer your
question in the abstract. Essentially what you would do in query design view
in this sort of scenario is to design a number of simple aggregating queries
to produce each set in the chain; firstly one to produce the set of lowest
prices over, grouping the query by something or other, then another based on
the first to produce the average of those prices grouping the query by
something else. The second grouping would most probably be a subset of the
first, e.g. you might group the first by project and item and return the
lowest (MIN) prices. Then in the then second you might group by item and
return the average (AVG) prices. You'd only need to restrict the first to
the last 13 months. This doesn't sound quite like what you describe as it
returns the average of the lowest prices per item per project (i.e. it allows
for multiple prices for the same item in one project), which is not the same
as 'average of the item with the lowest price per project'.
Ken Sheridan
Stafford, England
:
Ken I am very grateful for your continued correspondence, thank you Sir.
Query worked perfectly, it's not exactly the result I am working toward but
with your help I believe that I will be getting there soon. With the
statement you've provided it appears the returned value is the average of all
the prices for all the items. I am hoping to construct a statement that
returns the 13 month average of the item with the lowest price per project.
The lowest price for each item per project averaged over the last 13 months.
This will give an average of the lowest prices for each item.
I think that you were correct with your exiting statement last time,
serveral queries joined together into a single overall query.
You were also correct regarding the design vs sql view and query construction.
So, how can I build these queries if going into design view will probably
yield errors and my SQL logic and understanding is limited?
Best Regards,
-Matthew
--
http://njgin.aclink.org
:
I've just noticed that I correlated the innermost subquery with the outer
query unnecessarily. As the subquery is not now correlated there is no need
for the aliases TI1 and TI2. Also I assume the subquery would also need to
be restricted on biddate, so would need to join the tables in the same way as
the outer query. So the query should have been:
SELECT
itemnamedescription,
AVG(unitprice) AS [avg of unitprice],
MIN(unitprice) AS [min of unitprice],
MAX(unitprice) AS [max of unitprice],
COUNT(*) AS [count of tblItems],
(SELECT AVG(lowestprice)
FROM (SELECT MIN(unitprice) AS lowestprice
FROM tblProjects INNER JOIN tblItems
ON tblProjects.projectid = tblItems.projectid
WHERE biddate >= DATEADD("m", -13, DATE())
GROUP BY tblprojects.projectid))
AS [avg of lowest prices]
FROM tblProjects INNER JOIN tblItems
ON tblProjects.projectid = tblItems.projectid
WHERE biddate >= DATEADD("m", -13, DATE())
GROUP BY itemnamedescription;
To answer your question the query itself creates the 'lowestprice' column;
its not some existing object in the database. If you take a look at the
innermost subquery you'll see that MIN(unitprice) is returned as a column
named lowestprice, so the column contains the lowest unit prices grouped by
project for the current project returned by the outermost query. The average
of the values in this column is then returned by the first subquery with
AVG(lowestprice) and the column returned by this subquery is given the name
[avg of lowest prices]. As this is the average of the lowest prices for each
project the column will contain the same value in every row returned by the
query of course.
As regards the errors you will se there is no mention of 'False' in the
above query. False is actually a Boolean value, the opposite of True. I
suspect that a reference to a Boolean False has somehow crept into the query,
possibly as a result of your having opened it in query design view and then
amended it. When using subqueries its best to do everything in SQL view and
save it as such without ever going into design view.
On the basis of the table and column names in the original string expression
you posted you should be able to paste the above SQL into a blank query and
open it. I have in fact reconstructed your tables and tried it, and it opens
without any error. One thing I'm not quite clear about, however, is whether
by 'the average of the lowest priced item per project' you mean the average
the average of the lowest prices for any item per project, or the average of
the lowest price for the item returned in the current row of the query's
result table. I'd assumed the former and this is what the above query does.
If it is in fact the latter that's required I can't see how that could be
done in a single query because, having tried it, as far as I can see the
innermost subquery can't be correlated with the outer query. It would
probably need two (or maybe even three) queries joined in a single overall
query.
Ken Sheridan
Stafford, England
:
Wow Ken!
Thank you so much for your continued response.
I tried to load it into a button and execute but I got an error. I will
review this more closely, but I wanted to acknowledge and thank you for your
input as soon as possible. Thank you.
I get an error that reads false and then in another pop-it it reads,
