Help with queries

  • Thread starter Thread starter Alstar
  • Start date Start date
A

Alstar

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?
 
hi,
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
Click to expand...
Date is a name of a built-in VBA function and should not be used as
field name.
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.
Click to expand...
There are two questions remaining:

1. Why do you need to store it in a separate table?
2. Do you only want to select your records based on date and ip?

You need basically this selection query:

SELECT [date], [ipaddress]
FROM [yourTable]
GROUP BY [date], [ipaddress]
HAVING COUNT(*) > 1

Based on this query you can create a JOIN query like this to get the
site names:

SELECT DISTINCT [date], [ipaddress], [sitename]
FROM [yourTable] O
INNER JOIN
(
SELECT [date], [ipaddress]
FROM [yourTable]
GROUP BY [date], [ipaddress]
HAVING COUNT(*) > 1
) I ON I.[date] = O.[date] AND I.[ipaddress] = O.[ipaddress]

You can use this query as source query for an insert query:

INSERT INTO [yourOtherTable] ([ipaddress], [sitename])
SELECT DISTINCT [ipaddress], [sitename]
FROM [yourTable] O
INNER JOIN
(
SELECT [date], [ipaddress]
FROM [yourTable]
GROUP BY [date], [ipaddress]
HAVING COUNT(*) > 1
) I ON I.[date] = O.[date] AND I.[ipaddress] = O.[ipaddress]

Instead of creating this single query, you may store each query and use
it in the query builder to create the next one.


mfG
--> stefan <--
 
Back
Top