Access Query - Count. 1

[bostonkiwi]

Hi,

I have no idea about Access and SQL Queries but have the following task assigned to me!

I have the following:

1 access database with 1 table called RESULTS.

1 column of this database is called DATFILE - this is a text field with an entry for every row. Many of these rows contain the same information.

I need to do the following.

If an entry in DATFILE appears 100 or more times I would like to return all data relating to that particular datfile entry.

i.e. there might be 400 records for an entry en_pp9 in the DATFILE column. I would like all 400 entries returned for en_pp9.dbf. There might also be 1000 entries for en_wd9 in the column for DATFILE.

etc.

I have a query that returns the number of entries for each in datfile.

SELECT datfile, count(*) AS COMPONENTS
FROM results
GROUP BY DATFILE;

which retuurns the following:

datfile COMPONENTS
AUDAT 12
AUTYP 8
DATA 23955
EN_A10.DBF 25
EN_A2.dbf 155
en_a7.dbf 306
en_a97.dbf 113
en_aC9.dbf 366
EN_ACC.dbf 1305
En_ami.dbf 1
EN_C.dbf 3
en_cal.dbf 2740
en_chk.dbf 308
en_cod.dbf 43
EN_CPP.dbf 4
EN_CS1.dbf 1829
EN_CS2.dbf 707
en_f97.dbf 1
en_fil.dbf 1490
EN_frl.dbf 1
en_gn1.dbf 670
en_gn2.dbf 168
en_gn3.dbf 36
en_grm.dbf 425
EN_HTM.dbf 14
en_ie4.dbf 47
EN_IE5.dbf 446
EN_INM.dbf 113
EN_INS.dbf 98
EN_JAV.dbf 3
EN_L34.dbf 4
en_l5.dbf 15
en_L97.dbf 12
EN_LIT.dbf 488
en_lme.dbf 1
EN_LON.dbf 7
en_ltr.dbf 187
en_med.dbf 260
en_mth.dbf 445
EN_mts.dbf 1
EN_NN4.dbf 9
En_o10.dbf 79
EN_O97.dbf 33
en_off.dbf 74
En_ol9.dbf 208
EN_P10.DBF 132
EN_p4.dbf 10
EN_p7.dbf 210
en_p97.dbf 203
en_phn.dbf 5056
en_pp9.dbf 694
en_pr7.dbf 2
EN_PR9.dbf 1
EN_prl.dbf 3
en_PSY.dbf 5278
EN_pt8.dbf 52
En_qb1.dbf 111
en_rst.dbf 460
en_spl.dbf 2406
EN_STF.dbf 4
EN_stm.dbf 1702
en_voc.dbf 1505
en_w10.dbf 552
en_w2k.dbf 912
en_w31.dbf 15
EN_w6.dbf 128
en_w7.dbf 1489
en_w95.dbf 721
EN_w97.dbf 1589
en_w98.dbf 989
EN_wd9.dbf 3918
EN_WIS.dbf 73
en_wme.dbf 18
en_wp3.dbf 4
en_wp5.dbf 7
EN_wp6.dbf 18
EN_wp7.dbf 28
En_wp8.dbf 25
en_wp9.dbf 22
en_x10.dbf 573
en_x97.dbf 1630
EN_xl5.dbf 279
en_xl7.dbf 1481
en_xl9.dbf 3485
TENKEY 1914
TYPING 20352

Any help would be greatly appreciated.

 

[Golom]

Try
[tt]
SELECT *
FROM results
WHERE results.datfile IN
(SELECT datfile FROM results
GROUP BY datfile
HAVING count(*) >= 400)
[/tt]

 

[Golom]

Sorry ... that should be
HAVING count(*) >= 100

 

[scriverb]

Give this SQL a try:

Select A.* 
FROM RESULTS as A INNER JOIN qryResults as B on A.DATFILE = B.DATFILE 
WHERE B.COMPONENTS >= 100 
Order by A.DATFILE;

Post back with any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry, save your first query and call it qryResults.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[bostonkiwi]

Hey,

Thanks, this works GREAT!!

I have one more question......

Is it possible to create a seperate table for each entry in the datfile when you run the query.

For example, for EN_PP9 there is one table, for EN_WD9 there is another table with all the corresponding entries from RESULTS in each table????

Once again, Thank You!!!!

 

[Golom]

Well no ... at least not within a single query. If you want separate tables for each value of datfile (something I don't recommend) then you would need to write some code to do that.

You can of course, create some SQL that returns only those records that have a specific "datfile" value and you can use a query where you would use a table (mostly) so you can get the same effect as having separate tables.