Search results

Not sure how you change the date format in design view. I don't see a way to do that. Can you tell me what that involves.

Well I get to design view with this particular query, its grayed out. I assumed it was because this query was too complex to display in that view..that code was not accepted either...seems like such a simple thing to change the format...i guess not!

thanks. I tried writing the sql code into the select statement as such.. SELECT ([US Platinum].[Mailing Date])Format([DateField],"ddmmmyyyy")), Sum([US Platinum].[Unique click]+[US Gold].[Unique click]+[US GameClub].[Unique click]) AS AggregateClicks This looked right to me but keeps giving me...

Update...the tables are now corrected...its the queries that are giving me trouble with the formating...

Hi, I think I figured out the the issue. All the dates in the queries that its being grouped by are the same, yet the times are different. I have since tried to import the data with date only (also changing the design of the table to number or text), it still seems to revert back to...

Here you go... SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]+[US Gold].[Unique Click]+[US GameClub].[Unique Click]) AS AggregateClicks FROM ([US GameClub] LEFT JOIN [US Gold] ON [US GameClub].[Mailing Name]=[US Gold].[Mailing Name]) LEFT JOIN [US Platinum] ON [US...

Thanks, that was helpful. The results now display all the dates (which is close), but the other column (aggregate clicks) is blank. Do you know how I would get these results to display? Thanks.

Hi Paul- I tried the Union clause and wrote the following. Its basically the same query written twice with a UNION in between. This gives the same results as no Union clause at all. Any thoughts? SELECT Sum([US Platinum].[Unique click]+[US Gold].[Unique Click]+[US GameClub].[Unique Click]) AS...

Your right, the test records were different in some of the table so I corrected that and now those queries are working. So happy about that...thanks a million. As to the aggregate query...do you think I could fix that by simply changing the INNER JOIN to UNION?

The queries contain the result of email marketing campaigns. You're right the mail names are all unique. I'll try a union instead. That makes sense with the aggregate results. However, with the gold, platinum etc. queries would that be the same issue? You'll notice that those do have some...

Hi Paul, I zipped up this test db so it would fit. Maybe you can enlighten me as to why these queries are not updating with new data in the tables.

I think the file might be too big for this forum..

Attached the table design..Also, the issue with the from statement seems to be resolved with the second query from paul. However, i get zero results. The attached is the test data i'm using so i did expect to see something.

That makes sense. Thanks for writing that code. Do you happen to see the syntax error in this from statement? FROM ([US GameClub] INNER JOIN [US Gold] ON [US GameClub].[Mailing Name]=[US Gold].[Mailing Name]) INNER JOIN [US Platinum] ON [US Gold].[Mailing Name]=[US Platinum].[Mailing Name]

That did it as far as the error. Thanks. The results are still not what i need though. My logic must be off. Attached is what I would ultimately like to have. My query is only dealing with clicks but I'm just trying to keep it simple until I figure things out and can alter. Anyhow it still...

still getting that same error with these.. SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]), Sum([US Brown].[Unique Click]), Sum([US Gold].[Unique Click]) Sum([US GameClub].[Unique Click]) AS [SumOfUnique click1] FROM [US Brown], [US GameClub], [US Gold], [US Platinum]...

I thought it might have something to do with the parentheses, tried a few variations with them including this: SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]), Sum([US Brown.Unique Click]), Sum([US Gold.Unique Click]) Sum([US GameClub.Unique Click]) AS sum([SumOfUnique...

Thanks for the reply. I did it again as you suggested. SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click], Sum[US Brown.Unique Click], Sum[US Gold.Unique Click] Sum[US GameClub.Unique Click]) AS sum[SumOfUnique click1] FROM [US Brown], [US GameClub], [US Gold], [US Platinum]...

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click], [US Brown.Unique Click], [US Gold.Unique Click] [US GameClub.Unique Click]) AS sum[SumOfUnique click1] FROM [US Brown], [US GameClub], [US Gold], [US Platinum] GROUP BY [US Platinum].[Mailing Date]; This is a query written...

Just realized my error...left out sum...oops