Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 

< Previous: Access Expert 10

Next: Access Expert 12 >

Access Expert Level 11

Expert Microsoft Access Tutorial - 1 Hour, 48 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 10 left off. In this class we will learn how to use Aggregate Queries to summarize data. Topics include:
 
  - Aggregate Queries
  - Sum, Avg, Count, Max, Min
  - Complex Query Criteria
  - Sales Totals by Month
  - Find Lowest Product Cost
  - Last Customer Contact Report
  - Employee Work Log, Timesheet
  - Calculate Hours Worked
  - DSUM Function

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 11
Description: Access Expert Level 11
Versions: Recorded with Access 2013. Also use with 2007 and 2010. Everything in this class should work for all versions of Access.
Pre-Requisites: Access Expert Level 10 strongly recommended
Running Time: 1 Hour, 48 Minutes
Cost: $24.99


This class picks up where Expert Level 10 left off. We will start by learning about Aggregate Queries (also called Totals, Summary, or Grouping Queries). We'll learn about the various popular aggregate query functions like Sum, Avg, Count, Max, Min, First, and Last. We'll create a simple query to calculate sales grouped by state.

 

Next, we'll learn about some different types of complex query criteria. For example, we'll make a query to show customer credit limits, grouped by state, but only for customers who were added to the database after 1999. We'll learn how to break a date value down into its components with the MONTH, DAY, and YEAR functions. You'll learn about the Where option.

 

Next we'll go through several examples of different aggregate queries. First, we'll create a query to calculate sales totals by month. We'll learn how to use the Format function to put our dates in the "YYYY MM" format. We'll also break down our sales by week of the year as well.

 

Next we will create a query to calculate the lowest cost of a product from the many vendors we can purchase it from. Since we have multiple vendors that we can buy each of our products from, we want to know who has the lowest price for all of our products. We'll use the DLOOKUP function (that we learned about in the last class) to help with this one.

 

It's sometimes nice to have a list of all of our customers along with the last time we talked to them, and what was said. We'll make a customer list showing the last contact date along with the text of what was in that contact. If there was no contact, we'll put "No Contacts" there, courtesy of the IIF function.

 

We'll make an employee work log (time sheet) where we can clock each employee in and out. You'll see how to calculate hours worked per day, and total hours worked per week or month (or any two dates). You'll learn about the CDATE function to convert a text string into a valid date value.

 

Finally, we'll learn some other odds and ends, like the Expression option of the aggregate query, and how to use the DSUM function to add up a range of values from a table. We'll use it to put the total sales for the last 30 days right on our Main Menu. We'll make a Refresh button to update the calculation whenever we want.

 

This is the eleventh class in the Access Expert series. There's a lot of great material in this class. Learning aggregate queries will make your databases much more powerful. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 11

00. Intro (8:38)

01. Simple Aggregate Queries (13:13)
Aggregate Query
Totals Query
Summary Query
Grouping Query
What they are
Popular Aggregate Functions
SUM, AVG, COUNT, FIRST, LAST, MAX, MIN
Statistical Analysis
STDEV, STDEVP, VAR, VARP
Covered in future class
SUM in Form Footer Total
SUM in Table Footer (last row)
Totals Button
Simple SUM of all credit limits
Group By State
Default sort on Group By field
Sort by Credit Limit descending
Add criteria Is Not Null
Notice Field Name is Changed
SumOfCreditLimit
AvgOfCreditLimit
One query with SUM, AVG, MAX, MIN
ALIAS field names
Count of customers from each state
Only NON-NULL values are counted
Count ID field to include NULLs
Simple Query Wizard
Aggregate Options in Wizard
Summary Information

02. Complex Query Criteria (7:44)
Credit Limit by State After 1999
Brief overview of date functions
MONTH, DAY, YEAR Functions
HOUR, MINUTE, SECOND Function
Break into Two Queries
WHERE Field in Total Row

03. Sales Totals by Month (10:50)
Format to show "yyyy mm"
Format Property in Query Field
Format() Function
Week of Year ww
04. Lowest Product Cost (12:46)
MIN Cost for each Product
DLOOKUP Vendor ID at Lowest Cost
DLOOKUP Vendor Name, Unit Price
Calculate Most Profitable Products

05. Last Customer Contact (6:36)
MAX of Contact Date
DLOOKUP Contact Notes
Dealing with NULL Values
IIF Function Review

06. Employee Work Log (26:02)
Create Work Log Table
Time In, Time Out
Timeclock
Difference Between Two Times
Calculate Hours Worked
Caution with Dates and BETWEEN
BETWEEN #1/1# and #1/2#
Does NOT Include #1/2 5pm#
>=Start AND <End
CDATE() Function Brief Mention
Work Log Lookup Form
Employee Combo Box
Command Button Run Query
Forms!WorkLogLookupF!StartDate
Homework for Next Class

07. Miscellaneous (16:13)
Expression Option
DSUM Sales Last 30 Days
Refresh Form Data Button
Can't Refresh Unbound Form

08. Review (5:52)

 


 
Keywords: Aggregate Queries, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, aggregate query, totals query, summary query, grouping query, sum, avg, count, max, min, first, last, month, day, year, hour, minute, second, where, expression, format function, dlookup, dsum, iif, sales totals by month, lowest product cost, last customer contact, employee work log, timesheet, time sheet
 
 

Student Interaction: Microsoft Access Expert 11

Richard on 8/8/2013:  Microsoft Access Expert Level 11 is 1 hour, 48 minutes long. In this tutorial we will learn how to use Aggregate Queries to summarize data. Topics include: - Aggregate Queries - Sum, Avg, Count, Max, Min - Complex Query Criteria - Sales Totals by Month - Find Lowest Product Cost - Last Customer Contact Report - Employee Work Log, Timesheet - Calculate Hours Worked - DSUM Function Click here for more information on Access Expert Level 11, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 10.
Steven Schuyler on 8/14/2013: Richard:
In regard to the DSUM function, you showed an example of how to show sales for the last 30 days. I also want to see the total sales for the current year. I did some investigating and found the following solution:

=DSum("LineTotal","MenuMainF_CurrentMonthsSalesTotalQ","OrderDate>=DateSerial(Year(Date()), 1, 1)")

I hope this solution helps other users.

Steven S.
Berkeley, CA USA


Reply from Richard Rost:

Looks good. Thanks for sharing.

Jose Gutierrez on 8/19/2013: What is the fastest alternative to DLookUp when you are dealing with thousands of items or thousands of vendors?

Reply from Richard Rost:

I try to use DLOOKUP for small groups of data. If you have thousands of records, join the two tables together in a query first. It will run faster.

Ole Simonsen on 10/15/2013: My computer is running in Norwegian region.
This gives following errormessage when I run the "MostRecentContact2Q" :
Syntaxerror in Querryexpression CustomerID=1 AND DateTime=#09.05.2013 19:24:19.
I thougt it had with the use of # sign. Bu I can not find the settings for this.

Best regards Ole

John Borrelli on 10/17/2013: I know it will be fully covered in the SQL classes but in the misc you did the DSUM function for sales -30days. I did not understand the utilization of the " # and & in the OrderDate section. "OrderDate>=#" {why close quote here?} &Date()-30 & "#") is that read as OrderDate is greater than or equal to some date {end statement} Some date is today -30 and some date? Can you elaborate on this format structure?
Bruce Reynolds on 10/30/2013: The datediff function is also an undocumented function in Excel. For such a useful function, why has Microsoft chosen to not talk about it? Was datediff developed by a black sheep of the Microsoft programming family?
Alex Hedley on 11/3/2013: Bruce is this not a Legacy function from back in the Lotus days?

Alex

Hassan Abadi on 2/16/2014: Expert 11
course #4 Lowest Product Cost.
This code give me the #error message. Why?
All I changed is use CasePrice instead of UnitPrice.
VendorID: DLookUp("VendorID"," VendorxProductT"," ProductID=" & [ProductID] & " AND CasePrice=" & [MinOfCasePrice])

VendorName: DLookUp("VendorName","VendorT","VendorID=" & [VendorID])

Ray McCain on 3/24/2014: Since the end point does not include the date past midnight, it seems that a visual reminder would be in order for the benefit of the user. The label for end date could easily be modified to add 12:01 am, thus giving visual instruction, instead of relying only on verbal instruction.
John B on 4/14/2014: On the OrderF, would it be possible to figure out the profit for each line item and a profit total at the bottom kind of like the line total using Dlookup or using multiple queries? I have tried and Access told me it didn't recognize the SQL statement and did not recognize the syntax.

Reply from Richard Rost:

Sure. Subtract Unit Cost from Unit Price and then SUM that up in the footer.

Lynda Chase on 8/9/2014: Hi Richard,

For the life of me I can't get this DLookup to work can you please advise where I've gone wrong in this:
ContactNotes: DLookUp("Notes","ContactT","CustomerID=" & [CustomerID] & "AND DateTime=#" & [MaxOfDateTime] & "#")

This returns an error every time.
Thanks
Lynda

Reply from Richard Rost:

You're missing a space in a critical spot.

ContactNotes: DLookUp("Notes","ContactT","CustomerID=" & [CustomerID] & "AND DateTime=#" & [MaxOfDateTime] & "#")

Can you spot it?

Try replacing the field variables with values...

ContactNotes: DLookUp("Notes","ContactT","CustomerID=100AND DateTime=#1/1/2001#")

See it now?

Lynda C on 8/10/2014: Hi Richard,

I think it needed a space in front of the AND part of the blurb - which I did, but it's still reporting an error.

Reply from Richard Rost:

Aside from that space, your DLOOKUP statement syntax was correct.

Lynda Chase on 8/10/2014: I realise I've got this completely wrong, thought it would be extremely useful to have the 'dateupdated' field on the LowestProductPrice2Q, so attempted to add that in to my query - this is what I put:
astUpdated: DLookUp("VendorID","ProductXVendorT","ProductID=" & [ProductID] & "AND DateUpdated=#" & "DateUpdated" & "#")

Obviously I am struggling with the Dlookup function, could you please guide me as to where I've gone wrong with this as it's returning an error.

Many thanks

Reply from Richard Rost:

DLookUp("VendorID","ProductXVendorT","ProductID=" & [ProductID] & "AND DateUpdated=#" & "DateUpdated" & "#")

DateUpdated is a FIELD not a VALUE. It should not be in quote. And, you're missing a space in front of "AND" again.

DLookUp("VendorID","ProductXVendorT","ProductID=" & [ProductID] & " AND DateUpdated=#" & DateUpdated & "#")

Lynda Chase on 8/13/2014: Hi Richard, I've redone this several times now, using this: ContactNotes: IIf(IsNull([MaxOfDateTime]),"No Contacts",DLookUp("Notes","ContactT","CustomerID=" & [CustomerID] & " AND DateTime=# " & [MaxOfDateTime] & "#")) and the 'No Contacts' message is appearing where it should, but for some reason, the rest of the contact info is returning an error. I went back to my contactT to see if I could figure out why the error, but can't. The error message I'm getting is: Syntax error in date in query expression 'CustomerID=121 AND DateTime=# 01/08/2014 3:23:20 p.m.'.
I checked the notes, and there is definately notes for that datetime field in the contactT. Where do you think I may have gone wrong?
BTW, I would LOVE to be as savvy as you in Access, your knowledge is amazing. Thanks for the great, easy to understand, lessons.
Lynda

Reply from Richard Rost:

Again, it's all about spacing. This time you have a SPACE where there shouldn't be one:

AND DateTime=# " & [MaxOfDateTime] & "#"

This gives you:

DateTime=# 1/1/2000#

See the problem?


Lynda C on 8/13/2014: Hi Richard, I've changed it to eliminate the space - it now reads: ContactNotes: IIf(IsNull([MaxOfDateTime]),"No Contacts",DLookUp("Notes","ContactT","CustomerID=" & [CustomerID] & " AND DateTime=#" & [MaxOfDateTime] & "#"))
However it is still reporting the same error. Could there perhaps be something wrong in the table its referring to - I just need some guidance on where to 'look'. I appreciate also, that you don't just 'solve' my problem, you guide , which is fabulous!

Lynda

Reply from Richard Rost:

Does the DLOOKUP work WITHOUT being inside of an IIF function?

Lynda C on 8/14/2014: No - even without the IIF it still reports an error - with the IIF the 'no contacts' label does display, but error on everything else
Sam Finlay on 8/14/2014: Hi Lynda
Are you using non US regional settings ? To check if this is the problem change your regional settings to US date time and see if it then works.
I had the same problem and it turns out you need to enclose the MaxOfDateTime with a US Format I.E. Format([MaxOfDateTime],"mm/dd/yyyy hh:nn:ss") & "#"
It should now work with your local regional settings.

Lynda C on 8/18/2014: Hi Sam, Thanks for that, it is no longer reporting an error, but is not actually displaying the last contacts either - this is the current string?(is that what its called) I have:
ContactNotes: IIf(IsNull([MaxOfDateTime]),"No Contacts",DLookUp("Notes","ContactT","CustomerID=" & [CustomerID] & " AND DateTime=#" & Format([MaxOfDateTime],"dd/mm/yyyy hh:nn:ss") & "#"))

David Martinez on 8/20/2014: I found this lesson a bit confusing. As someone who is just trying to learn the ideas and concepts, not build a database specifically for a sales company, I had trouble following along with this particular lesson. A bit more explanation of what the goal was and what was being accomplished as the video progressed would have been appreciated.



Reply from Richard Rost:

I can appreciate that. Not everyone will find each lesson useful. You may never have a need for an aggregate query, or to sum up monthly sales total. However, being exposed to the material may cause you to realize later that you CAN accomplish one of your goals by using these things. Sometimes just exposure to a topic can increase your awareness of what CAN be done. Sometimes you don't know WHAT you don't know. :)

sam Finlay on 8/20/2014: Hi Lynda
You need to swap the dd/mm part of the date to mm/dd That should do it.

Brian Merrick on 8/23/2014: I have made a vehicle database to keep track of the miles on the vehicle by day and by week. I want the total miles by week. How would i do that including the date.

Reply from Richard Rost:

Unfortunately, Access doesn't have a WEEK function like Excel does, but you can easily make your own as a CALCULATED QUERY FIELD:

Week: Format(MyDateField,"ww")

Now you can use that Week value in your AGGREGATE query. (This is covered, by the way, in Lesson 6 of this course... so keep watching).

Lynda Chase on 9/10/2014: Hi Richard, I've finally got the query showing the most recent contact for each customer. My problem now is that it is displaying in the query in plain text, not rtf. The linking ContactT has the notes memo field set as rich text, could you please advise where I'm going wrong with this?

Thanks
Lynda


Reply from Richard Rost:

Try displaying the results in a FORM. Set the field to Rich Text.

Lynda Chase on 9/14/2014: Hi Richard,
My customer table is linked to a contact table that shows the 'managers' of different locations.
I am trying to add a button to my customercontactF which will open up my communications form with two where criteria (customerID and contactid) So i can not only see customer communications, but communications with the relevant customers employee. I've tried figuring this out by googling...this is what I've come up with...
DoCmd.OpenForm "CommunicationF", , , "CustomerID='" & Me.CustomerCombo & "' AND ContactID='" & Me.ContactCombo & "'"

This isn't working - could you please tell me where I'm going wrong.

Thanks
Lynda


Reply from Richard Rost:

So your CommunicationF has both a CustomerID and a ContactID on it, and you want to show records for BOTH? In that case, you need an OR condition, not an AND condition.

Docmd.Openform "CommF",,,"CustomerID=" & X & " OR ContactID=" & Y



Lynda Chase on 9/14/2014: Hi Richard

Is there a way to get the 'new record' in a continuous form, to appear at the top, rather than the bottom of the list?

Reply from Richard Rost:

Nope. Not that I'm aware of. Best you can do is issue a Docmd.GotoRecord command to jump to a New Record.

Lynda C on 9/16/2014: Hi Richard, I'm wanting both fields to drop in on the CommunicationF by default

Reply from Richard Rost:

What do you mean by that?

Lynda C on 9/16/2014: Hi Again,

I mean when I am on the customers contact form, I want to click a button, that opens the CommunicationF, and have both the ContactID and CustomerID fields populate with the relevant details from the ContactF.


Reply from Richard Rost:

How does Access know this?

Without knowing how your database is configured and what these forms MEAN, I'm really of little use here. Can you give me this question using generic terms? I don't understand what your CommunicationF and ContactF do and what they're based on.

Lynda C on 9/16/2014: Hi Richard

There are three forms involved here.

My CustomerF - which has all my customer info

My ContactF - which shows people and their positions within the relevant CustomerF e.g Esme Cole - Sales Manager, Keith Cole - Property Manager etc

and then I have my CommunicationF - which reports all communication between myself and the 'Customer'.

What I have done, is added both a customerID field AND a ContactID field to my Communications form - so I can not only see the customer I'm communicating with, but also, who, within that Company I spoke to.

So, I have combo boxes set up on the Communications Form. I have also added a button to my 'ContactF'. My ContactF has both the Contacts details, as well as the Company they work for (CustomerIDCombo)

When I click on the 'add new communication button from this form, I would like the communication combo boxes for CustomerID and ContactID to automatically populate, based on the ContactF I have clicked from.

I hope that makes sense.

Reply from Richard Rost:

OK... see you've named your "ContactF" something completely different than I did, which is why I was confused.

All you should have to do is set the Default Value properties for the two ID fields on your CommunicationF form to the ID values on the other form:

=Forms!CustomerF!CustomerID

If the ContactF is a subform on the CustomerF then that would be

=Forms!CustomerF!ContactF.Form!ContactID



Alex Hedley on 9/16/2014: If [ContactID] and [CustomerID] are on [ContactF] then when you open the form [CommunicationF] have their "Default Values" set to
=[Forms]![ContactF]![CustomerID] and
=[Forms]![ContactF]![ContactID]

Alex

Brian Farley on 9/22/2014: Is there any benefit or penalty to use the database joins we have setup. For instance. The second query would have:

LowestProductPrice1Q,
VendorXProductT,
VendorT,
ProductT

All left joined by the relative IDs and MinOfUnitCost-> VendorXProductT.UnitCost. Then you can just drop the fields you need in the query without the DLOOKUP.

Maybe that would be faster then DLOOKUP with tons of records? Or maybe I'm jumping ahead..

Keep up the great work :)

Reply from Rick Rost:

Brian, that course is over a year old, so I don't remember EXACTLY what the lesson covered, but it is generally faster to work with linked tables than to use DLOOKUP in a query for a large set of data. I'm sure if that's the way I did it in class, however, there must have been a good reason for it. :)


Brian Farley on 9/23/2014: Worth Noting that if you use the WorkDay field, the one we used to group by. The between function WILL include all of the times in the EndDate.

My guess is because the WorkDay is a calculation and its rendered as a text or numeric field rather then a date.

So to avoid the CDate, training and +1 issues, maybe just better to search by WorkDay.

--just spitballin

:)

vicki Hudson on 9/24/2014: At 5:10 When I Run the Query is tells me "The specified field [UnitPrice] could refer to more than one table listed in the FROM clause of your SQL statemnt." I understand that [UNITPRICE] is in two tables but I thought I had followed the lessons exactly. Do you not have the [UNITPRICE] in OrderDetailT and ProductT? If so, what is my solution to fix that error? thanks
SQL view = SELECT OrderT.OrderDate, [Quantity]*[UnitPrice] AS LineTotal, OrderT.IsPaid
FROM (OrderT INNER JOIN OrderDetailT ON OrderT.OrderID = OrderDetailT.OrderID) INNER JOIN ProductT ON OrderDetailT.ProductID = ProductT.ProductID
WHERE (((OrderT.IsPaid)=True));


Reply from Rick Rost:

Hi Vicki.

If you have the same field in multiple tables you would need to specify which table you want to pull the value from, in this case either OrderDetailT.UnitPrice or ProductT.UnitPrice

vicki Hudson on 9/24/2014: I came back to this lesson from lesson 7 because my OrdersForStatsQ gave me an error saying the [UnitPrice]could refer to more than one table listed in the FROM of SQL. I came back to see where the left joins were made and corrected that in my db but now get the same error message in my SalesByMonthQ. Lost and bewildered
Jonathan Mainardi on 10/8/2014: To include the end date (around 26:00 into the video), can't you just use <= rather than use CDate() and +1?

Reply from Alexander Hedley:

Dates are tricky because of the Time element +1 just makes sure the end date is included, if you prefer <= you can use that.

CHARLES FULGHAM on 12/18/2014: Mr. Rost, Thank you for all that you do, I'm learning so much! Quick Question: In Expert11, Video 6, at 23min48sec...we are assigned homework to create a Printable Time Sheet for Employees, Date, Time, TimeIn, TimeOut, Regular Time, Overtime, Overtime Rate etc. I cannot find where this homework was ever solved, as I have become stumped trying to complete it. THANK YOU!

Reply from Alex Hedley:

Hi Charles,
Normally the homework is covered in the first lesson of the next course.

Tom Dlugosh on 2/19/2015: I have a Textbox on a subform that uses the following Control Source statement: =DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32"). If the particular product doesn't have a record in ProdLabT yet an error is returned in a field on the main form that references the field on the subform. Is There something else I need to do?

Reply from Alex Hedley:

How about wrapping it in a NZ() function?

Tom Dlugosh on 2/20/2015: I've tried Nz, isnull and IIf...Is Null statements to no avail. If there is no record entered in the table for that ID number an error is returned. As soon as I enter a record the field that references that data in the table returns the correct information and the other fields that do not evaluate to 0.

Reply from Alex Hedley:

Can you show me the code you used for each please

Tom D on 2/20/2015: I've tried =IIf(DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32") Is Null, 0, DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32")
and I've tried =NZ(DSum("[EstLabCostExt]", "ProdLabT", "[ProdID] =" & [tboProdID] & " AND " & "[OpsID] = 32"), 0).
I've also tried using if..else..end if in vba on an unbound field. All with the same result - #Error! in the field in question.
I guess I could create a bogus record for the queried tables each time I create a new Product (there would be 3), but that doesn't seem to be the proper way to fix this problem.

Reply from Alex Hedley:

Let me do some more tests and get back to you

Spiros Poulis on 4/12/2015: At section 4(02:23) you make a query that returns for each product the offers from vendors. My question is if there is a way to limit the offers not to the min cost, but to make a query that returns for each product the 3 lowest offers. I have for example five or more offers but I want to make a query that returns only the first minimum 3 offers for each product.

Reply from Alex Hedley:

There is an option in the ribbon for a TOP clause. It is a dropdown, probably says all at the moment.
You have make this a percentage or a number.
This is covered in Beginner Level 5
You can put in 3 here and have an Order By statement so the lowest are at the top

Spiros P on 4/13/2015: Dear Sir, the solution that suggests does not select the top minimun 3 offers from each product, but only the 3 min offers.

Reply from Alex Hedley:

Forgot the Group By (Σ) then choose Min.

Ferida Oe on 7/29/2015: Where should I send the homework? Sorry I joined the course not from beginner or maybe you could refer me in which video that I can get the information about it.

Reply from Alex Hedley:

You could post it here but I'm not sure if anyone ever has.
Usually the answer is covered in the next lesson.

Jeffrey Ervin on 8/24/2015: Hi

Im Going back on lessons for employees and work logs, as with previous comment by CHARLES FULGHAM, the home assigned at 23mins was not fully resolved eg. Hours - reg pay, ot Pay, etc. was this covered somehere elsewhere?



Reply from Alex Hedley:

Have you gone through Expert 12?

Brian Jensen on 12/29/2015: Hi Richard,
In your example of dealing with returned errors by using iif(isnull, can this method also be used in the group by catagory to name the null grouping to something like "# of customers without contacts", this would only be when counting contacts by customer. When I try it for my example I get a error "The specified field [Shoplist] could refer to more than one table listed in the FROM clause of your SQL statement. Below is my SQL

SELECT IIf(IsNull([ShopList]),"No Shop Assigned",[ShopList]) AS ShopCountList, Count(EmployeeCombinedQ.EmployeeID) AS CountOfEmployeeID
FROM EmployeeCombinedQ LEFT JOIN ShopListT ON EmployeeCombinedQ.ShopListID = ShopListT.ShopListID
GROUP BY IIf(IsNull([ShopList]),"No Shop Assigned",[ShopList]);


Reply from Alex Hedley:

Does [ShopList] appear in both the Table and the Query that you're joining?

Try adding either
ShopListT.[ShopList]
or
EmployeeCombinedQ.[ShopList]

Access doesn't know which one you are referring to.

Timo Knaepper on 1/7/2016: Hi Rick. I am just curious about the "Orders in the last 30 days" field on the Main Menu. Does that amount account for discounts given? I have used "ExtPrice2" from "OrderDetailQ" to get the Total which accounts for discounts given.

Reply from Alex Hedley:

Refresh my memory, what does the DLOOKUP use?

Timo Knaepper on 1/8/2016: The Control Source is =DSum"LineTotal","OrdersForStatsQ","OrderDate>=#" & Date()-30 & "#").The OrderForStatsQ uses OrderDate from OrderT, LineTotal: [Quantity]*[UnitPrice]and IsPaid from OrderT.

Reply from Alex Hedley:

I don't think the LineTotal includes the discounts so if you wanted it to your ExtPrice2 Field would be better

Andrew Washington on 3/16/2016: Do you cover how to query the most recent date when the data is not able to be grouped? For instance, I'd like to present the most recent date that someone took a test, but I'm having trouble because the test scores are all different so it returns the most recent date for every different score. Tried breaking it into two queries, doesn't work. Can't find it addressed adequately anywhere in this galaxy.

Reply from Alex Hedley:

You could use a Top 1

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP