I created a form that contains two combo boxes plus other information. The first combo box contains the following items: (Claim Under $5000, PIR/CPRA, Potential Claim, CA Vic Comp, Summons&Complai nt, Request to produce docs). The second combo box contains the following items ( Vehicle Damage, windshield claim, not state Highway, ORIM, Contracts, Districts). Both combo boxes are related. I need to create a query that counts and displays how many Claims under $5000 were processed in total and how many of those claims under $5000 are for Vehicle Damage, Windshield Claim, Not state Highway, ORIM, Contracts, or Districts. I need to do the same for each item of the first combo box. PLEASE PLEASE Help. I have no idea if I have to do something in the AfterEvent option in the combo box. What will be the easiest way for a newbie like me to do this.
-
-
Not sure I understand your question fully
if the form is single record
the record control box at the bottom of the form shows
record n of NumRecs
If the form is continuous
open the footer of the form
add a textbox to the footer and in its control source property type
=count(FieldNam e)
where FieldName is the name of one of the fields in the form -
It sounds like you want to count the occurrences of each in the data underlying the form (I can't see what else would make any sense if I'm honest).
If so, do you want the value of the count of the matching records to be shown on the form whenever a new selection is made?
Do you use the two ComboBoxes to filter the underlying data as it is?Comment
-
Hello to all, I was so frustrated last night with this assignment because I could not find any info online. I need to count the occurrences for each one of the items. If the user selects "Claim under $5000" from the first combo box and "Vehicle damage" from the second combo box, it will count 1 and 1 for each item and so forth. Then, I need a query that shows the following:
1. Claim under $5000 Total Occurrences:
(under this category)
-Vehicle Damage Total Occurences:
-windshield Claims Total Occurences:
-Not State Highway Total Occurencees:
- ORIM Total Occurences:
etc...
2. PIR/CPRA Total Occurences:
(under this category)
-Vehicle Damage Total Occurences:
-windshield Claims Total Occurences:
-Not State Highway Total Occurencees:
- ORIM Total Occurences:
etc...
Please do not think that I want you guys to do my assignment. I just need some guidance, I read that I need to use combo boxes for thos two lists, but I do not know what I need to do to count the occurences and where I need to place that command (combo box or query). Your help is appreciated greatly.Comment
-
Take a look at the DCount Funtion in Access. Each one of those values is criteria that can be used by that function to count records based on that criteria value. You can do it different ways but as I see it you essentially have a field called 'Claim' and 'Claim Type' something like that. Both are inter-dependant.
The Dcount function is fully documented in Access help.
If you were more advanced I would suggest created a modular function of your own where the count would be done using a one column recordset returning the value to a function which could then be embedded in a query that serves as the rowsource for your dropdown thus giving you the extra column capability I mentioned before., but I think at this stage it might be too much to go withComment
-
Yes I think it necessary that you understand the DCount Aggregate function it is one of many aggregate functions in Access. Look them up in help 'Aggregate functions' They are very commonly used bread and butter stuff. As you become more advanced then you can progress into understanding 'other' counting concepts essentially more efficient but I don't want to overload you with this...step by step is much better I think. Let us know how you go on :)Comment
-
Hello Jim, thank you for your consideration. I am trying to pass my access class and I do not even know why I need to take this class as I am a Business Admin. student. I admire each one of you for knowing all of this because this can be challenging.
Ok...this is what I am trying to do:
I understand that I need to store the occurrences of each item in a different place, so I created text boxes on the form for each one of the items...correct ?
Then, in the control source for my first text box, I am trying to add the Dcount function as follows:
DCount ("[Claims]","Claimant ","[combo14] = 'Claims under $5000'")
Claims=My table
Claimant= my form
Combo14= it's the combo box that contains the type of file
Claims under 5000= it is my first item of my combo box
This is not working yet, because the text box is displaying: "#Name?" . At least I would like to know if I am on the right track?Comment
-
Just as a clarification - These functions are generally referred to as Domain Aggregate Functions, as opposed to the Aggregate Functions, which pertain to those functions you can use from within SQL to aggregate across a GROUP BY clause. They are easily differentiated by starting with a D (for Domain).
DLookup(), DSum(); DCount(); etc
Whereas Sum(); Count(); Max(); etc are Aggregate Functions.
Sorry Jim. Not trying to be argumentative, and I'm sure you know this already. I just wanted to clarify in thread for the less experienced users.Comment
-
Hi again
In your study of the functions concerned (the elements of which I need not repeat as they are full documented in help) and given that it 'is' a matter of studying and understanding their arguments. I have prepared a little db for you to look at that lists data in a claims table in line with your post.
Have a look at the drop downs how they interact, their row sources and the selection of calculated fields that are on screen. Each textbox has a function in it to perform either a count or sum based on the functional arguments.
Remember none of this 'calculated numeric' data is stored in the table because these ARE calculations on the fly, the results of which in themselves need 'not' ordinarily be stored as static physical data.
Remember with the Domain aggregate functionality you are essentially doing this - you are 'aggregating' the result of a column of physical data NOT as in your last post [combo14] is not relevant in that context is not correct. Study and extract the attached db contained in zippped format and you will get the idea.
RegardsComment
-
Thank you. I will check it out.Comment
-
Hello Jim,
Your file was very helpful. I think I finally got it. Thank you!!Comment
Comment