--------------------------------------------------------------------------------

Hi Guys,

This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query.
Any help will be highly appreciated!!

Here is what I need...

I have relatively small ms acces database with about 1000

I have 3 colums

date ipaddress sitename

12/09 34.3.3.3 A
12/09 34.3.3.3 A
12/09 34.3.3.3 A
12/09 33.4.4.4 B
12/09 33.4.4.55 C


What i need is if an ipadress is recorded more than 2 times in a single month for instance (34.3.3.3) put those ipaddress with the Site-Name in a seperate table.

Any ideas?

You will need two queries
First query a Group By to count the number of times the IP address is recorded for the month.

Something like this
SELECT tbl_test_data.period, tbl_test_data.isp_add, Count(tbl_test_data.site) AS isp_count
FROM tbl_test_data
GROUP BY tbl_test_data.period, tbl_test_data.isp_add
HAVING (((Count(tbl_test_data.site))>2));

period is your date e.g. 12/09 do not use Date as a field name as it is a reserved word.
isp_add is your ipaddress e.g. 34.3.3.3
isp_count is the count for the number of times that your ipaddress occurs during the month.
The Having clause will only show where the number of times is > 2

Second Query - To append the site names we now need to join the first query to your table with the following
INSERT INTO tbl_sites ( period, isp_add, site )
SELECT tbl_test_data.period, tbl_test_data.isp_add, tbl_test_data.site
FROM tbl_test_data INNER JOIN qry_totals ON (tbl_test_data.isp_add = qry_totals.isp_add) AND (tbl_test_data.period = qry_totals.period);

I have attached a sample database for you. note, I have period as text you will need to change it to the correct format.

Hi Poppa!!!

Bro I dont know how to thank u .. you are the greatest I mean to take time off and help me out.. you are a GEM and thanks for attaching that database too ur the KING of access as far as I am concerned!!!

-A

as helpful as PS was - - and it was very helpful - - nonetheless you should at least be aware that what you ask is a database no no. One does not write data from one table to another ( at least in terms of database best practices ...)

why? .... lets say the data changes in your first starting table......and what was a duplicate before is no longer a duplicate...then you have to delete your data from the second table....

in your case this may never ever be the case - since it looks like an autostamp log file of some sort... but readers should know the pitfall of your request....

the db-correct reply is simply to do a date range bound duplicates query of the first table any time you need to see duplicates within the month.....