Calculation help

[gary g]

I have a report that lists the total number of mediation cases we have processed, along with other related relevant data for each record. The report is sorted so that the individual mediators are listed in order by their last name. This way we are able to easily keep track of the number of mediations processed by each of our three mediators, which is the purpose of the report.

For example: When the report is opened, in alphabetical sort order from top to bottom, J. Doe's name appears 24 times, indicating he has mediated 24 cases; then J. Evan's name is listed 61 times, indicating he has mediated 61 cases; and finally J. Wagner's name is listed on the report 15 times, indicating he has mediated 15 cases.

The problem: Reading the report one has to manually count the number of cases under each name to determine the number of cases each has mediated. Instead of doing a manual finger count, is there a way to create a query, and an ensuing report, that will just show the physical number of cases mediated by each person. For example: J. Doe: 24; J. Evans: 61; J. Wagner:15? I am sure there is a calculation that will give me the result I am looking for, but I need assistance creating it.

I presume the calculation would count the number of times each person's name (field name=FacilitatorAssigned) appears in the underlaying table, and then give the result. How would I structure such a query?

Many thanks for anyone's assistance.

Gary G.
Houston, TX

 

[jjturner]

Hello and Welcome,

In a query:
SELECT FacilitatorAssigned, Count([FacilitatorAssigned]) AS CaseCount
FROM myTable
GROUP BY FacilitatorAssigned;

But if you wish to retain the details of each record, you can build the grouping and counts into the Report itself using the grouping levels and an aggregate formula (although it's been a good while since I've fussed with Reports).

HTH,
John

 

[gary g]

Thanks for the reply. I created a new query, selected the field named FacilitatorAssigned from the underlaying table, and entered your information under criteria. However, when using your query I get an error message stating "the syntax in a subquery is incorrect."

Can you look at it one last time to confirm it is correct. Or perhaps type in bold exactly what I need to enter under criteria in case I entered it incorrectly. Wouldn't I need to enter somewhere in the code the names of each facilitator that I am trying to get a count on? For example, the names Doe, Evans, and Wagner. Otherwise, how would it know what terms to search for to include in the count? Just thinking out loud.

Thanks again.

Gary

 

[jjturner]

Hi Gary,

What I listed was the entire query. Go to SQL View and simply paste everything in there.

HTH,
John

 

[gary g]

John,

I keep getting a syntax error in the From clause whenever I enter the name of my table (I substituted the text myTable in your code with the actual name of the underlaying table the query is pointing to). And I think therein lies the problem. The database I am working with was created many years ago by someone who did not follow any kind of proper naming conventions whatsoever. I just took over management of the databases a few months ago and am stuck with them pretty much as they are. The name of the table in question is Mediation Micro Tracking March 2009 The name is a mouthful and I think that may be causing the problem with syntax errors. Any suggestions to get around it? I tried entering [table name] as a prefix hoping it might force some kind of table recognition, but to no avail.

I appreciate all your help. If we can't find a fix then I'll just live with what I was given.

Gary

 

[jjturner]

You're on the right track - use square brackets to enclose your table name:
[Mediation Micro Tracking March 2009]

Any time a space is included in the name of an object in Access, it's treated as a delimiter unless it's encased within special characters (i.e., [], "", etc.)

Cheers,
John

 

[brickelldb]

Having a similar problem, however I am looking to pull additional data of the record, but NOT in a report, just in the query itself......(so i can update with the additional data to another table)......

My query is as follows:

SELECT Tbl_PriceCheck.ItemID, Min(Tbl_PriceCheck.Cost) AS MinOfCost
FROM Tbl_PriceCheck
GROUP BY Tbl_PriceCheck.ItemID;

BUT I ALSO NEED: VendorID from Tbl_PriceCheck to show up in the above query because I need to update another table with the lowest price along with the VendorID related to that lowest price.

I have tried adding it through the SQL statement as well through query design but I get the error "You tried to execute a query that does not include the specified expression "VendorID" as part of an aggregate function"........

Do I need to link the query without the VendorID and relate it back to the original table when doing my update to pull that additional info? Or can it be done in the same query expression? THANKS!!! BEEN BUSTING MY HEAD OVER THIS. CAME ACROSS THIS THREAD.

 

[jjturner]

The error is because you didn't include VendorID in your GROUP BY clause:
GROUP BY Tbl_PriceCheck.ItemID, Tbl_PriceCheck.VendorID;

Of course you're probably already including VendorID in your SELECT clause, as appropriate (otherwise it wouldn't throw you that error)

HTH,
John

 

[brickelldb]

I had done what you mentioned in the past; however the query results gave me multiple records for the same ItemID...when my goal is to pull the lowest price for each ItemID.

The table: Tbl_PriceCheck contains columns: ID, ItemID, VendorID, Price

In Tbl_PriceCheck, the same ItemID can exist several times for each vendor who carries it and with their price for that Item.

I want the query to ONLY show the vendor with the lowest price for each item. Therefore, ItemID should never repeat itself in the query results....which is what is happening when I do what you mentioned (as i had originally done this query). My query needs to show ItemID, VendorID, Price...........and only 1 record for each ItemID along with the price and vendorID for the lowest price.

 

[brickelldb]

I came across this on a google search. I guess it's not possible what I'm trying to do. But I wanted to thank you anyways for your help! Its much appreciated!

"As you proceed, remember that by default, a totals query can include only the field or fields that contain your group data, such as a "categories" field, and the field that contains the data that you want to sum, such as a "sales" field. Totals queries cannot include other fields that describe the items in a category. If you want to see that descriptive data, you can create a second select query that combines the fields in your totals query with the additional data fields."

 

[jjturner]

ok - I see what you mean. This is a common dilemma. Subqueries can be one way to tackle this -

Try:

SELECT DISTINCT tPCMain.ItemID,
  (SELECT TOP 1 tPC.Cost
   FROM Tbl_PriceCheck tPC
   WHERE tPC.ItemID = tPCMain.ItemID
   ORDER BY tPC.Cost) AS MinOfCost,
  (SELECT TOP 1 tPCV.VendorID
   FROM Tbl_PriceCheck tPCV
   WHERE tPCV.ItemID = tPCMain.ItemID
   ORDER BY tPCV.Cost) AS MinCostVendorID
FROM Tbl_PriceCheck tPCMain;

 

[brickelldb]

Ahhhhh, I see the light; however, I am running into errors.

1) Do I need to create the tPC and tPCMain tables as mirrors of the Tbl_PriceCheck table?

2) When I ran the query (even after going ahead and creating the 2 tables I asked about in ques # 1) I get an error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

 

[jjturner]

By renaming the original table "Tbl_PriceCheck" directly in the Query, you're creating an alias for it. If you look in Design View it should show your table name as "tPCMain" - it's using the alias.

So leave the SQL statement as-is without adding any tables.

The only thing you'll probably need to fix is extra spaces, which I only threw in on the post just to make it look half-way presentable on-the-quick.

Let me know how it goes...

 

[brickelldb]

OK I see what you mean.

However, I am still getting that error

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." and then it highlights FROM

Looked through and everything seems to be in oreder.....

 

[jjturner]

my bad - take out the comma after "MinOfCost"

 

[brickelldb]

There we go! MUCH APPRECIATED.

Let me ask you, it gives me a message "only 1 record can be returned at a time with this subquery"------so I gave a criteria for a particular ItemID and it returned the info I wanted (which is GREAT). But is there anyway that I can return info for all items. So I can turn around and update a table that contains all of those ItemID's with the lowest price that your query returned?

 

[jjturner]

Your main query should be returning a list of all ItemID's in your table, along with all associated VendorID's. If you have equal values for MinOfCost, it will return both so maybe try modifying your Subquery:
(SELECT DISTINCT TOP 1 ... )

Hopefully, that gets rid of the message you're getting (TOP predicate doesn't return just 1 record if the value it determines for MinOfCost is present in more than 1 record)

In any case, I presume you want to update the "Cost" for each ItemID/VendorID combo with "MinOfCost" ?

Sounds like that would essentially invalidate/erase your previous Cost values from your table? Are you sure you want to do that? Why not just keep your original Cost values (presuming they have some sort of legitimate reason to be in your table in the first place) and generate the MinOfCost recordset whenever you need to run reports, etc.?

Anyway, if you absolutely must, you could just build an Update Query off the results from this present query joined to your Tbl_PriceCheck on fields ItemID and VendorID.

HTH,
John

 

[brickelldb]

That's the direction I was headed. Excellent. Thank you again, Much appreciated!

 

[hotrodsue]

I also am having a similar problem with a query. My query, QryNetPounds is based on 2 queries QryHarvestLoadSum and QryRecentTareAverage, which are joined by Date and VehicleID.

Data is entered for 3 harvest dates: 03/30/2009, 03/31/2009, and 04/01/2009. No results return for 04/01/2009. QryNetPounds returns results when BOTH dates match in the queries joined by date.

That makes sense to me. However, what I need to do is get results on 04/01/2009 when there is NOT a matching Tare Date. In that case, I’d like it to use the most recent Tare Date entered which is 03/31/2009.

Is there a way to specify using the most recent tare date if the dates don't match? I'll keep reading and working, any ideas are appreciated.

In searching this forum, I found this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=137883

It contains a lot of helpful information. However, I don't need only results returned. I need to have a value from the most recent date used to perform a calculation in the results. Does that make sense? Can anyone help? If you need more info, please let me know.

 

[jjturner]

brickelldb,

You mention that you continue to get the message "only 1 record can be returned at a time with this subquery" - so I'm curious what your data values are.

For same ItemID, can more than 1 VendorID have the same minimum price?

You should be getting all ItemID's coming back from the query, so I don't know any other reason why it would only return 1 record, as you say. And it's prompting you for a value for ItemID?

If possible, please post your db and I'll see if I can't figure things out...

HTH,
John