Too many results

  • Thread starter Thread starter I Am
  • Start date Start date
I

I Am

Hi. I'm pretty much an amateur with Access, but I understand the basics of
query.
I'm trying to wade thru some data to get a report on where my PPC hits have
resulted in actual sales.

I'm starting small, so I have 2 data files right now, both pre-massaged to
weed
out a lot of un-necessary data. The first file is from my raw server logs,
and may
contain several instances of the same person hitting my site by clicking my
ads. (grrrrr)
The 2nd file contains sales data from my shopping cart. The IP address is
how I'm matching my data.

The problem lies here. Say a user clicked an ad twice and made a purchase
once
(assume for now these occurred on the same day). My query returns 2
records instead of 1.

Here's my query:

SELECT DISTINCT [Retail-MTD].[Invoice], [Retail-MTD].[Price], [Overture
Clicks].[Address], *
FROM [Retail-MTD], [Overture Clicks]
WHERE [Overture Clicks].[Address]=[Retail-MTD].[CustomerIP] And
[Retail-MTD].[Price]>0
WITH OWNERACCESS OPTION;

In my file Overture Clicks I have 2 records like this:
Address Date
66.999.888.77 [11/Dec/2003:09:17:56]
66.999.888.77 [11/Dec/2003:10:45:23]

And my Retail-MTD would have this record:

Order# Day Month Year Price ..... CustomerIP
23454 1 12 2003 37.95 ..... 66.999.888.77

So this query is returning 2 results, because of the the 2 entries in my
Overture Clicks file. Can I somehow re-define
this query? My first thought was to use DISTINCT, but that's not really the
problem. I thought about trying to use the
dates, but it's not a feasible solution either. I really don't want to have
to filter out my Overture Clicks file to use
only unique records if I don't have to. Why? Those records contain search
parameters and other identifying
remarks to let me know which ad produced the results.

Any suggestions?

Thanks
 
have you tried running a totals query and doing a groupby
on the customerid field and doing a count on your sales
field and on your clicks field. this way it will tell you
the number of times a person has clicked on your site and
the number of sales it has resulted in.

To get a totals query, go to design view on your query,
right click and select totals. then in the totals field in
the query selcet groupby for customer id and count for the
other two fields.
 
Back
Top