Count tables for a new guy.

[th1nk1nk]

I have been using excel to attempt a task where there is just too much data. This being my first time using access I apologize for lack of knowing anything.
In excel i would have all my text values in a column, the next would have a (=countif) on a range of values using the text column.

So far I have set two tables up as described below in access. The first table will end up having its text values permanent and end up at about 25 million records.


The count I would like to update when i choose as my range of values in the second table will change completely about once a month and will require a new count.

I am trying to create two tables. One will have the data i would like counted set up something like this (we will call it vals for now)

id, tex(text value), happened(number and or count?) |
1 ab (?)
2 bb (?)
3 ca (?)

The second will be a list of values as they have come out. (for now i call this table data )

id, dats (text) |
1 ab
2 ab
3 ab
4 ca
5 ab


My goal is to have the "happened.vals" column be updated to how many times each of the "tex.vals" has come out in the entire "dats.data" table.



Thank you for any help. I assume when my tex field gets to the millions and checking out against the dats field it will become time consuming but as it stands I have done the same idea with excel and i have close to five 180mb tables all doing the same thing and summarizing the data has got to be too much. My pc has a six gig processor and eight gigs of ram but excels 1.48 million row limit has done me in. I am not concerned how long the task will take as each of my excel files normaly takes an hour or so a piece but having to come back to do the same thing on each table is getting to be a pain. I would prefer to set it and forget it and see the result as a whole.

[ConneXionLost]

Hi,

Generally it's a bad idea to store calculated values in a table. For calculations, Access uses queries.

In your situation, you only need your data table; let Access give you your "counts" in a query.

Cheers,

[weekend00]

to continue ConneXtionLost's
you don't need the vals table, use following very query instead:

select dats, count(dats) as happened from data group by dats

since this is the first time you use Access, please let us know if you need step by step on how to create a query.

[th1nk1nk]

Ok, so far a good start Thank you ive gone from lvl 1 dummy to lvl 1.5 .
The reason i think i need the second table is that my range of values
will in some ways differ. The first table will be values that will not duplicate.
example
ab 3
bb 3
cb 0
db 0
eb 1
gb 0
and my second table will end up having a range of records that changes from time to time with multiples of those values in table 1
example
ab
ab
ab
bb
bb
bb
eb

So i am try to count against. I have described things simply as i could to start but the issue is my static values might have to count against there range in some situations where instead of just "ab" it could be "ab-bb" or "ab-eb". In either of those situations i would require any of the static values that occur in the range to be counted.
I am fooling with things a bit myself but its not going so well.
I know its a bit much to throw out there but the eventual step will have my first table having combinations of things and I know its going to get hairy.

I will try to attach my excel with very limited data that does the same thing and see if that helps explain things a bit in the even that either of you knows excel. Just note that the count is in fact a formula and i have set the file so that its on manual calculations due to the fact that Ive been dealing with maxed out rows.
Also the data sheet is more true to the types of data i will be dealing with.



Once again thanks for all the help and i hope that you can somewhat understand what i'm getting at.

[ConneXionLost]

In your Excel spreadsheet, you appear to be counting (with COUNTIF) occurrences of text strings within text strings. Access does the same thing with the InStr function.

In the situation where you want to identify several/many strings to look for, then you would likely use another table for this as you've done in Excel. Your data table would be represented by your named range "countedrange", and your "static values" from the range A2:A10 would be in the second table. You would use a query to provide what you have in A2:B10 as the query results.