Triming info and remove duplicates

[James]

Ok, I have a database full of usernames and websites they visit.
it lots everything like http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim everything before
and including the second \ and evertyhing after the 3rd \. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the database though

How would I do this?

Thanks

 

[Suzette]

Pretty much you would need to be able to create another field so that you
can do the trimming through code. It's pretty hard to do in report
formulas. Next do a DISTINCTROW query to only pull one record for each site
then do the report on that.

Suzette

 

[MarkD]

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark

 

[James]

Ok, thats great, now I'm VERY new to access, how would I go about using this
Would this go in a query or where would I put this?

Thanks

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark

-----Original Message-----
Ok, I have a database full of usernames and websites they visit.
it lots everything like http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim everything before
and including the second \ and evertyhing after the 3rd \. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the database though

How would I do this?

Thanks


.

 

[Ernie]

You would put this as criteria in a query, or on your
report as it's Filter.

Take note however that it will stumble over secure
websites which start with HTTPS:\\

To account for this replace Mid([website],8 with
Mid([website],InStr([website],Len([website]),"\\") + 2 in
the formula below.

InStr gets the first position where "\\" is in the website
string, adding 2 to this position places you at the first
character of the actual website name.

-----Original Message-----
Ok, thats great, now I'm VERY new to access, how would I go about using this
Would this go in a query or where would I put this?

Thanks

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark

-----Original Message-----
Ok, I have a database full of usernames and websites

they
visit.

it lots everything like http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim everything before
and including the second \ and evertyhing after the 3rd \. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the database though

How would I do this?

Thanks


.

.

 

[James]

When I try typing it in as criteria in a query I get:
The expression you've entered contains invalid syntax.

I've changed all the websites to the field name...
What am I doing wrong?

You would put this as criteria in a query, or on your
report as it's Filter.

Take note however that it will stumble over secure
websites which start with HTTPS:\\

To account for this replace Mid([website],8 with
Mid([website],InStr([website],Len([website]),"\\") + 2 in
the formula below.

InStr gets the first position where "\\" is in the website
string, adding 2 to this position places you at the first
character of the actual website name.

-----Original Message-----
Ok, thats great, now I'm VERY new to access, how would I go about using this
Would this go in a query or where would I put this?

Thanks

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark



-----Original Message-----
Ok, I have a database full of usernames and websites they
visit.
it lots everything like
http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim
everything before
and including the second \ and evertyhing after the 3rd
\. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the
database though

How would I do this?

Thanks


.

.

 

[James]

Ok, well I'm not sure how to do this
I've tried this, which doesn't work

SELECT
Mid(WebProxyLog1.uri,8,InStr(Right(WebProxyLog1.uri,Len(WebProxyLog1.uri)-
7),"\")-1)
FROM WebProxyLog1;


What do I need to do differently?

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark

-----Original Message-----
Ok, I have a database full of usernames and websites they visit.
it lots everything like http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim everything before
and including the second \ and evertyhing after the 3rd \. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the database though

How would I do this?

Thanks


.

 

[MarkD]

Try this
1) Open up a new select query.
2) click on the sigma sign (looks like a funny 'E')
3) copy what I have below into a query field, changing the
term [website] to the field which contains your url.

hope this helps,
-Mark

Note: If you have some VB experience, you can create a
function that you can call from a query.

-----Original Message-----
Ok, thats great, now I'm VERY new to access, how would I go about using this
Would this go in a query or where would I put this?

Thanks

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark

-----Original Message-----
Ok, I have a database full of usernames and websites

they
visit.

it lots everything like http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim everything before
and including the second \ and evertyhing after the 3rd \. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the database though

How would I do this?

Thanks


.

.

 

[James]

This does not work
Just returns all websites as ERROR

You would put this as criteria in a query, or on your
report as it's Filter.

Take note however that it will stumble over secure
websites which start with HTTPS:\\

To account for this replace Mid([website],8 with
Mid([website],InStr([website],Len([website]),"\\") + 2 in
the formula below.

InStr gets the first position where "\\" is in the website
string, adding 2 to this position places you at the first
character of the actual website name.

-----Original Message-----
Ok, thats great, now I'm VERY new to access, how would I go about using this
Would this go in a query or where would I put this?

Thanks

Hi,

assuming you always start with the "http:\\", you can do a
group by on this expression:
==============
Mid([website],8,InStr(Right([website],Len([website])-
7),"\")-1)
==============

Basically, it looks at the 8th character onwards, finds
the first "\", and returns everything in between.

hth,
-Mark



-----Original Message-----
Ok, I have a database full of usernames and websites they
visit.
it lots everything like
http:\\www.website.com\graphichs\pic1.jpg
http:\\www.website.com\graphichs\pic2.jpg
http:\\www.website.com\side\pic3.jpg
http:\\www.website.com\top\pic4.jpg

When I run a report I want see that the person went to:
www.website.com. Thats it, So basically I want to trim
everything before
and including the second \ and evertyhing after the 3rd
\. Then I want to
remove all duplicate records.
I don't want the actual data to be changed in the
database though

How would I do this?

Thanks


.

.