Count Values in Fields (1 Viewer)

basilyos · Oct 6, 2018

hello

i have a table contains two fields
first field = Date
second Field = codes

01/01/2017 | 105, 201
05/03/2017 | 105, 901
01/01/2018 | 105, 201
07/08/2018 | 901,902

One Code

i want to count the code 901 from today and one year back so i will get (1)
105 from today and five years back i will get (3)

Multiple Code

105 and 201 from today and five years back i will get the sum (5)
105 and 201 from today and five years back i will get seperated (3) (2)

any help??

June7 · Oct 6, 2018

So, do you mean 365 days past from today? You show only 4 codes, how many are there really? This is not a normalized data structure. Is that a simple text field or a multi-value field?

Keep in mind, accounting for leap years will likely require a custom function to calculate the past date value.

basilyos · Oct 7, 2018

This is a sample data
I have have multi-value field
No need for special function because i just want ro fo back 365 or 730. I just want to go back by days

June7 · Oct 7, 2018

Do you understand how to use multi-value field in query? Review https://support.office.com/en-us/ar...-queries-6F64F92D-659F-411C-9503-B6624E1E323A

Consider:

SELECT Table1.Codes.Value, Count(Table1.Date) AS CountOfDate
FROM Table1
WHERE (((Table1.Date) Between Date()-365 And Date()))
GROUP BY Table1.Codes.Value;

Date is a reserved word and should not use reserved words as names.

basilyos · Oct 7, 2018

Thank you it works very well
but still need one thing plz

this solution count the codes seperatly
for example i want to sum the code 901 and code 105 or any two or three codes, should i make a new query or could we update this one ?

basilyos · Oct 7, 2018

Any help guys??
Thanks

Pat Hartman · Oct 7, 2018

This would be a simpler problem if your data were normalized. The additional complication is that you have two different date ranges.

Select Count(*) as CodeCount from yourtable
Where (YourDate Between Forms!yourform!FromDate1 and Forms!yourform!thrudate1 and Code Like "*" & Forms!yourform!Code1 & "*")
AND
(YourDate Between Forms!yourform!FromDate2 and Forms!yourform!ThruDate2 and Code Like "*" & Forms!yourform!Code2 & "*")

This query will return only a count.

CJ_London · Oct 7, 2018

@Pat - the OP is using a multivalue field - just thinks because it s displayed as a single field, that is what it is.

@basil, you need a modification to Junes code to provide the 'grouped sum'

Code:

SELECT Count(*) AS Occurences
FROM Table1
WHERE Table1.Date Between Date()-365 And Date() AND Codes.Value =[Enter first code e.g.201] OR Codes.Value=[Enter second code e.g. 901]

basilyos · Oct 7, 2018

Thank you all
And specially @CJ you rock man

I appreciate all your help

Count Values in Fields (1 Viewer)

basilyos

Registered User.

June7

AWF VIP

basilyos

Registered User.

June7

AWF VIP

basilyos

Registered User.

basilyos

Registered User.

Pat Hartman

Super Moderator

CJ_London

Super Moderator

basilyos

Registered User.

Similar threads

Users who are viewing this thread