Count a field and Count Unique occurrence of field

[2Plan]

How can I count the next to last column of the dataset below and count the unique occurrence of the next to last column of the dataset below? Typical dataset is about 200,000 lines; so I would have to use a command to open the ascii file and then count.


'2010-07-28 18:04:55.000',1,2214,7,33,1,1,1160,100
'2010-07-28 18:32:31.000',1,2056,7,33,1,1,1160,100
'2010-07-28 20:26:04.000',1,8916,101,33,1,1,1160,100
'2010-07-28 21:03:17.000',1,8935,7,33,1,1,1160,100
'2010-07-29 08:46:13.000',1,8917,7,33,1,1,1125,100
'2010-07-29 09:22:59.000',1,2067,350,33,1,1,859,100
'2010-07-29 10:01:07.000',2,6024,339,33,1,1,859,100
'2010-07-29 10:37:11.000',1,2211,7,33,1,1,1160,100
'2010-07-29 10:41:53.000',2,6024,339,33,1,1,859,100
'2010-07-29 11:18:28.000',1,8909,300,33,1,1,859,100
'2010-07-29 11:50:19.000',2,2034,339,33,1,1,1160,100
'2010-07-29 12:53:09.000',1,8938,1,33,1,1,1126,100
'2010-07-29 13:05:09.000',1,8919,7,33,1,1,1148,100
'2010-07-29 13:10:33.000',2,2029,339,33,1,1,1160,100
'2010-07-29 13:18:56.000',1,7424,331,33,1,1,1148,100
'2010-07-29 13:19:43.000',1,8917,7,33,1,1,1126,100
'2010-07-29 13:51:03.000',1,8930,7,33,1,1,1160,100
'2010-07-29 14:22:41.000',1,7433,17,33,1,1,859,100
'2010-07-29 14:54:47.000',2,2028,4,33,1,1,859,100

 

[dhookom]

Can you tell us what your expect output would be based on your above sample records? Do you want the results in a query or code or report or where?

Duane
Hook'D on Access
MS Access MVP

 

[2Plan]

I would the output/results to go to a *.txt file. For example the output/results file would read:

Total Riders = 192,000
Unique Riders = 3,100

 

[dhookom]

Any "counts" of your data should result in 19 or less since you can't count more than the total number of records.

Where do your numbers come from and how do they relate to the sample records?

Could you put field names to your columns of numbers so we are totally confused?

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Seems to me the easiest way to get the totals/counts you want would be to move
the data into a table. Then, simple queries will provide the desired results.


Randy

 

[2Plan]

Sorry for the confusion:
I've listed the column headers.
I need total count of seq column.

According to the dataset below, Output "results.txt" file should read:

Total Seq Count = 19
Total Unique Seq = 5

ts,loc_n,bus,route,ttp,grp,des,seq,tpbc
'2010-07-28 18:04:55.000',1,2214,7,33,1,1,1160,100
'2010-07-28 18:32:31.000',1,2056,7,33,1,1,1160,100
'2010-07-28 20:26:04.000',1,8916,101,33,1,1,1160,100
'2010-07-28 21:03:17.000',1,8935,7,33,1,1,1160,100
'2010-07-29 08:46:13.000',1,8917,7,33,1,1,1125,100
'2010-07-29 09:22:59.000',1,2067,350,33,1,1,859,100
'2010-07-29 10:01:07.000',2,6024,339,33,1,1,859,100
'2010-07-29 10:37:11.000',1,2211,7,33,1,1,1160,100
'2010-07-29 10:41:53.000',2,6024,339,33,1,1,859,100
'2010-07-29 11:18:28.000',1,8909,300,33,1,1,859,100
'2010-07-29 11:50:19.000',2,2034,339,33,1,1,1160,100
'2010-07-29 12:53:09.000',1,8938,1,33,1,1,1126,100
'2010-07-29 13:05:09.000',1,8919,7,33,1,1,1148,100
'2010-07-29 13:10:33.000',2,2029,339,33,1,1,1160,100
'2010-07-29 13:18:56.000',1,7424,331,33,1,1,1148,100
'2010-07-29 13:19:43.000',1,8917,7,33,1,1,1126,100
'2010-07-29 13:51:03.000',1,8930,7,33,1,1,1160,100
'2010-07-29 14:22:41.000',1,7433,17,33,1,1,859,100
'2010-07-29 14:54:47.000',2,2028,4,33,1,1,859,100

 

[2Plan]

Randy,

Can I move a 200,000 line dataset into an Access table?

Thanks.
2plan

 

[MajP]

There is no row limit. Only limit is 2GB max. If that is your only day should be able to get millions of rows.

 

[randy700]

And, if you want to output it to Excel, upgrade to 2007.

Randy

 

[2Plan]

Correct MajP. I just did a test.

So if I import the dataset into an access table what simple query would give total seq count and total unique seq count at the same time?

 

[dhookom]

I would first create a totals query like:

SELECT Dataset.Seq
FROM Dataset
GROUP BY Dataset.Seq;

Then create a union query like;

SELECT "Total Seq Count" AS Title, Count(Dataset.Seq) AS CountOfSeq
FROM Dataset
GROUP BY "Total Seq Count"
UNION ALL
SELECT "Total Unique Seq", Count(*) FROM qgrpSeq;

You will get something like:
[tt][blue]
Title CountOfSeq
Total Seq Count 19
Total Unique Seq 5
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You might also want
SELECT tblData.Seq, Count(tblData.Seq) AS CountOfSeq
FROM tblData
GROUP BY tblData.Seq;

so you can see the break outs

Seq	CountOfSeq
859     6
1125    1
1126    2
1148    2
1160    8

 

[2Plan]

Thanks Randy,

Were upgrading to Excel 2007 next month!

 

[MajP]

BTW you do not have to import the text file into a table, you can link to it externally as well.

 

[2Plan]

You guys are great! Thanks for all the info!

 

[2Plan]

Just curious, how many rows can we export to excel 2007?