Count with condition Queries

[Nikar]

Well I did some search but i didnt find anything that helps me, here it goes:

I have a Access database with over 30.000 inputs with this format:

Date Number
01/02/09 1
01/02/09 5
01/02/09 20
01/02/09 25

02/02/09 25
02/02/09 20
02/02/09 20
02/02/09 6
..
..

There are 20 different numbers each day.

I have a table that contains all the possible 3 number combinations with
this format(it can be changed):

Number1 Number2 Number3
5 20 25

So i want access to count how many times each combination has repeated
itself in the same day.

In this case it would count that the combination (5,20,25) has repeted
itself 2 times.
The numbers listed go from 1 to 99 and they dont repeat per day.

If anyone has some tip or idea I can try i would be very helpful. I know
some excel but it doesnt help much.

Thanks a lot, if you need further explanation or a pice of the database just
let me know.

 

[Nikar]

I couldnt find the edit button:

02/02/09 25
02/02/09 5*
02/02/09 20
02/02/09 6

 

[MGFoster]

Well I did some search but i didnt find anything that helps me, here it goes:

I have a Access database with over 30.000 inputs with this format:

Date Number
01/02/09 1
01/02/09 5
01/02/09 20
01/02/09 25

02/02/09 25
02/02/09 20
02/02/09 20
02/02/09 6
.
.

There are 20 different numbers each day.

I have a table that contains all the possible 3 number combinations with
this format(it can be changed):

Number1 Number2 Number3
5 20 25

So i want access to count how many times each combination has repeated
itself in the same day.

In this case it would count that the combination (5,20,25) has repeted
itself 2 times.
The numbers listed go from 1 to 99 and they dont repeat per day.

If anyone has some tip or idea I can try i would be very helpful. I know
some excel but it doesnt help much.

Thanks a lot, if you need further explanation or a pice of the database just
let me know.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you mean "how many times the combination repeats over more
than one day" since your data expresses that idea, not what you have
stated.

If the table that holds the number combinations is changed to this:

NumberCombos:
combo_nbr - the combination identifier
nbr - the number(s) in the combination

Data would look like this:

combo_nbr nbr
1 20
1 25
1 5
... etc. ...

And the entered numbers were in a table called NbrEntries with columns
"entry_date" and "nbr" - then you could use this query:

SELECT C.combo_nbr, E.entry_date
FROM NumberCombos As C INNER JOIN NbrEntries As E ON C.nbr = E.nbr
GROUP BY C.combo_nbr, E.entry_date
HAVING COUNT(*) = 3

This will give you results like this (using your data):

combo_nbr entry_date
1 1/2/2009
1 2/2/2009

If you wanted to count the number of days that had the same number
combinations you could write a query that would use the above query.
Something like this:

SELECT combo_nbr, COUNT(*) As number_of_days
FROM query_name
GROUP BY combo_nbr

Results would look like this:

combo_nbr number_of_days
1 2
... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeUhh4echKqOuFEgEQLXngCeIqLWmnsqs096sXoXUouyphIDW6EAn2GM
SL8TJxfyVa9SWv6DwVagHYZM
=UmW6
-----END PGP SIGNATURE-----

 

[Nikar]

Thanks a lot, it worked perfectly C:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you mean "how many times the combination repeats over more
than one day" since your data expresses that idea, not what you have
stated.

If the table that holds the number combinations is changed to this:

NumberCombos:
combo_nbr - the combination identifier
nbr - the number(s) in the combination

Data would look like this:

combo_nbr nbr
1 20
1 25
1 5
... etc. ...

And the entered numbers were in a table called NbrEntries with columns
"entry_date" and "nbr" - then you could use this query:

SELECT C.combo_nbr, E.entry_date
FROM NumberCombos As C INNER JOIN NbrEntries As E ON C.nbr = E.nbr
GROUP BY C.combo_nbr, E.entry_date
HAVING COUNT(*) = 3

This will give you results like this (using your data):

combo_nbr entry_date
1 1/2/2009
1 2/2/2009

If you wanted to count the number of days that had the same number
combinations you could write a query that would use the above query.
Something like this:

SELECT combo_nbr, COUNT(*) As number_of_days
FROM query_name
GROUP BY combo_nbr

Results would look like this:

combo_nbr number_of_days
1 2
... etc. ...

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeUhh4echKqOuFEgEQLXngCeIqLWmnsqs096sXoXUouyphIDW6EAn2GM
SL8TJxfyVa9SWv6DwVagHYZM
=UmW6
-----END PGP SIGNATURE-----

 

[John Spencer]

I'm not sure this will work at all, but you might try it and see if you
get any reasonable result

SELECT Ba.Date, T.Number1, T.Number2, T.Number3
, Count(Ba.Date)
FROM ((TableNumbers as T INNER JOIN BigTable as Ba
ON T.Number1 = Ba.Number)
INNER JOIN BigTable as Bb
On T.Number2 = Bb.Number)
INNER JOIN BigTable as Bc
ON T.Number3 = BC.Number
WHERE BA.Date = Bb.Date
AND Bb.Date = Bc.Date
GROUP BY Ba.Date, T.Number1, T.Number2, T.Number3

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Nikar]

Well atually i did some tests with my database and when i run your queries I
get a Access error that says that the Querie can not be finished because of a
space limit of 2gb.

I do have a lot more of harddrive space and ram and virtual memory.

Do you have any other ideas to try or any fix to the performance of the
querie?

 

[John W. Vinson]

Well atually i did some tests with my database and when i run your queries I
get a Access error that says that the Querie can not be finished because of a
space limit of 2gb.

Have you Compacted the database? Some of that 2gbyte could be dead space left
over from previous runs.

 

[Nikar]

Have you Compacted the database? Some of that 2gbyte could be dead space left

over from previous runs.

Well my database is arround 8mb, I compacted it but it still gives me the
same error when I run the querrie