Access Q&A: Totals, DCount, Concatenation
By Richard Rost
19 years ago
More recently answered Access questions:
I'm trying to create a report that will list all entries in a particular field together, one under another, without repeating the label.
For example, I have a table that consists of a series of questions - Name, Favorite Color, Favorite Food, Pet, etc. I'd like the report to simply list the results for each question under my label. Each record should be on a different line, but listed neatly, one after the other.
So I'd make a text label for "Favorite Food" and all I'd like to see is a running list of every Favorite Food entry in my table. (It does not need any other associated data, such as Name) Then I'll make another text label for Favorite Color, and see a list of all the colors entered into the table.
I'd like to avoid having to make a separate Query for each question, because there are a lot of them. Each table has 15-20 questions.
Any help would be appreciated! Thanks!
From your description it sounds to me like you just need to get a unique list of values from your table. For example, if you have a whole bunch of customers in the database, you need to get a list of all of the unique CITIES in the customer table. So if you had:
Joe Smith, Chicago
Bill Jones, Chicago
Will Alan, New York
Doug Parker, Houston
Mya Smith, Chicago
Your final list would need to look like this:
Chicago
New York
Houston
Am I on the right page? If this is what you need to do, then you simply need to create an AGGREGATE query. Essentially, create a new query, add the field you want to it, and then turn on the TOTALS row (the button on the toolbar looks like an "E" - or Greek letter Sigma). Then in the new totals row, you'll see the option is set to "GROUP BY." Leave it. Now when you run the query, you'll see a unique list of all the STATES in your customer table.
Unfortunately, yes, you'll have to do this for every field you want to get a unique list of values for. I don't know of any other way to do this short of some VBA programming, which is possible, but it's more involved than I care to go into here.
I cover AGGREGATE queries in exquisite detail in my Access 221 tutorial:
/site/courselist/access/access221
I have a combobox that selects a name, the VBA code appends on a 2 digit number a ":" - so I may get "Stephen07:". This is the substring I want to search the table (tblNames) in one field (txtNames) for all records that start with the substring, and return a count of the number of matches - or zero if there are none. I have tried doing a manual query using 'Like "Stephen07:"' as the parameter, and it does return the matches (but not a count). How do I do this from the VBA code for the onClick of the combobox? Please be specific - I am new to Access > Stephen
Sounds like DCOUNT would probably be the best way to solve this. You're already familiar with VBA code, so you should be able to figure it out.
Essentially, DCOUNT says "count all the values in a table or query." So, for example, if you want to count all the customers in a table that have the last name SMITH, you would say:
X = DCOUNT("*","CustomerTable","LastName='" & SMITH & "'")
If you wanted to count all of the orders after 1/1/2007, you would say:
X = DCOUNT("*","OrderTable","OrderDate >= #1/1/2007#")
Easy enough?
I answered another question for someone a little while back on how to use DCOUNT. See:
/blog/display-article.asp?ID=54
I cover DLOOKUP which is a very similar function in my Access 302 class: http://www.599cd.com?GOAC302
I cover DMAX which is almost exactly like DCOUNT except it returns the MAX value instead of a count. That's covered in Access 306: http://www.599cd.com?GOAC306
Hi Richard,
i would like to illustrate my question based on a practical example.....hope you will understand...
1. i have an excel file located at the address below:
"c:\2007\10Jan.xls"
i want to access this file by clicking on the command
button of a form.
2. i have declared three variables as:
global a as variant
global b as variant
global c as variant
a = 10
b = "Jan"
c = 2007
3. to access the excel file i use the below syntax on the
"on click" event of the command button:
application.followhyperlink "c:\2007\10Jan.xls"
Richard, i want to know how can i use the variables a,b,c in the above syntax instead of \2007\10Jan
awaiting for your reply.....
This should just be a matter of constructing a string with concatenation:
MyFile = "C:\" & c & "\" & a & b & ".xls"
Then you can use the SHELL() command to open Excel:
Shell "C:\PATH\EXCEL.EXE " & MyFile, vbNormalFocus
I cover String Concatenation in detail in my Access 104 class: http://www.599cd.com?GOAC104
|
