Grouping & Count Value in Access

[benedictine191]

Hi, could anyone help me please?
currently i tried to make access report based on queries that i've been made.

what i want in the report is
i want to group the claim/no claim value based on channel/md/dealer record.

for example the output, i tried attached it at the "hasil.png".

so it group the channel and only count the value of the claim and no claim...

thank you and sorry if my english is bad for the explanation

 

[sneuberg]

What determines whether it is a claim or a no claim?

 

[benedictine191]

What determines whether it is a claim or a no claim?

claim or no claims is determined when
we input date,
then i compared the sell out date and input date,
if the sell out date > than input date, it produce claim
and if sell out date < input date, it produce no claim

from that report, i tried to count the claim and no claim value by each MD.
but from what i got in the report, it count all the value which is claim or no claim.

 

[sneuberg]

I can't find the "sell out date" nor the "input date" in either table in the database you uploaded. Where are they?

 

[sneuberg]

if the sell out date > than input date, it produce claim
and if sell out date < input date, it produce no claim

What if they are the same date?

 

[benedictine191]

I can't find the "sell out date" nor the "input date" in either table in the database you uploaded. Where are they?

i didnt put them in the table, i just show them as a query, and the report i run it from my query.
if the sell out date = input date, then the result is also claim,
if the sell out date is NULL, then the result is also claim.

did the problem is i dont put it on the table?

 

[sneuberg]

If these date are not in some table you can join into the query, and there's no other way to determine claim or no claim, you can't do this? How would you expect to?

Where is this information currently located?

Note that if these dates were available you could get what you want easily with an iff expression

 

[benedictine191]

If these date are not in some table you can join into the query, and there's no other way to determine claim or no claim, you can't do this? How would you expect to?

Where is this information currently located?

Note that if these dates were available you could get what you want easily with an iff expression

currently the input date is defined by the user, if they input it today, then they just input 20151203 into the field.
and after they input the date, i run the query so it automate compare the input date with sell date and produce "claim" or "no claim"

but because im new to access, could you tell me how if they want to insert date they want into the table?

meanwhile I just create input_date,sell_out date, claim,no claim field
then they just insert it when they running the access..
thankyou

 

[sneuberg]

currently the input date is defined by the user, if they input it today, then they just input 20151203 into the field.
and after they input the date, i run the query so it automate compare the input date with sell date and produce "claim" or "no claim"

Ok you could take the input date from a user input on a form and put that in the query, but the sell date would still have to be in one of the tables. Maybe the database you attached is missing something. How are you "automate compare the input date with sell date and produce "claim" or "no claim""?

 

[benedictine191]

Ok you could take the input date from a user input on a form and put that in the query, but the sell date would still have to be in one of the tables. Maybe the database you attached is missing something. How are you "automate compare the input date with sell date and produce "claim" or "no claim""?

Hi Sneuberg, i've found the problem here,
lately i tried copy the output of the query, the claim and no claim value is count even when they are null,
but when i convert their data type in the excel, it count right,
do you know how to convert the data type in the query?
so when i call it on the report, the datatype is true.

 

[sneuberg]

do you know how to convert the data type in the query?
so when i call it on the report, the datatype is true.

Convert what datatype to true? Please give me an example query with data and the output you want.

 

[sneuberg]

Hi Sneuberg, i've found the problem here,
lately i tried copy the output of the query, the claim and no claim value is count even when they are null,

I don't understand how Access could be counting Nulls. Could you upload something that demonstrates this.

 

[benedictine191]

I don't understand how Access could be counting Nulls. Could you upload something that demonstrates this.

neither do I
i tried to export the data from the "testing" queries, and the result is like this.
try to download the file, and count the claim and no claim column, you will see that the output is counting the blank column wheither it's contain data or no...

 

[sneuberg]

The file you attached when converted into an Access table doesn't have nulls where you see empty cells, but instead has empty strings. Empty strings are counted. I've attached a database with this converted Excel spreadsheet. The Convert Empty Strings to Null query in this shows you one way you can convert a column that these empty strings to one with nulls.

You could convert the empty strings to nulls with an update query. This is probably something you should do as these empty strings can cause all sorts of problems.

 

[benedictine191]

Hi,

i tried your query but it produce the same, they count the null records,
here i attach the output you produce, and what I want...
if by your queries, it also count the field....the result are 15732 records,
the right results must be 15282

:banghead::banghead::banghead:

 

[sneuberg]

I've added a query (Count of Convert Empty Strings to Null) to the attached database which counts the coln ClaimWIthNulls and the result is 15282. So I don't understand what you mean.

I don't think I can help you. Maybe some other forum member will pick up this thread or you can repost your problem with a different question, one that perhaps is understandable.