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 from other queries. I attached an example of the "Platinum" query. Each one I am using has the same colums. I am trying to get the sum of each catagory (US Brown, Gold Platinum etc.) and create a new aggregate query.

Getting an error with the Select satement..Thanks

You have the Sum() around all 4 fields; you need one around each.

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]
GROUP BY [US Platinum].[Mailing Date];

Still getting a syntax/missing operator error.

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 click1])
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date];

For some reason I am still getting the same results.

I am? You'll also need to drop the sum here:

As sum[SumOfUnique click1]

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]
GROUP BY [US Platinum].[Mailing Date];

thanks for the help all.

You're missing a comma. Try this:

SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]) AS Platinum, Sum([US Brown].[Unique Click]) AS Brown, Sum([US Gold].[Unique Click]) AS Gold, Sum([US GameClub].[Unique Click]) AS GameClub
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date]

I would expect joins between those tables, or you're going to get a Cartesian product.

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 giving me the sum of each instead of aggregating the results for each column and grouping by day. It seems the select statement needs altering so that I select sum(all queries.clicks) instead of each individually. But I don't know that there is a way to do that. Or maybe I'm wrong.

Ah; try

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 AggregateClicks
FROM [US Brown], [US GameClub], [US Gold], [US Platinum]
GROUP BY [US Platinum].[Mailing Date]

Would it be too much to ask if you could supply your table structures? Example:

US Platinum
----------
Mailing Date
Unique click

US Brown
----------
Mailing Date
Unique click

Come to think of it, you could probably do this too:

Sum([US Platinum].[Unique click] + [US Brown].[Unique Click] + [US Gold].[Unique Click] + [US GameClub].[Unique Click]) AS AggregateClicks

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]

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.

Can you post the db? I don't understand how the tables relate to each other. I wonder if they should be unioned rather than joined?

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

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.

Can you describe what the queries contain, in general terms? You are joining on the mailing name, but there are none in common, which is why you get no results. My gut is these need to be brought together with a UNION query, not joined. Or just aggregated from the raw tables.

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 results (although they cut off after a certain date inexplicably), but they are also joined by a unique identifier in the serial column. Thanks again for looking at these.

My guess would be it "cuts off" due to the joins and the nature of the data. I notice that some mailings do not have records in all 4 tables. What exactly do you expect this query to show you?

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?

No; a UNION has a different structure, and does a different thing:

SELECT...
FROM...
UNION ALL
SELECT...
FROM...

Basically it's going to stack those records vertically, while a JOIN will put them horizontally. Knowing what you expect to see will help us choose the right one.

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 AggregateClicks, [US Platinum].[Mailing Date]
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]
GROUP BY [US Platinum].[Mailing Date]
UNION ALL SELECT Sum([US Platinum].[Unique click]+[US Gold].[Unique Click]+[US GameClub].[Unique Click]) AS AggregateClicks, [US Platinum].[Mailing Date]
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]
GROUP BY [US Platinum].[Mailing Date];

i think you need to change the top SELECT clause to have LEFT JOIN instead of INNER JOIN, and the bottom SELECT to use RIGHT JOIN (or the other way around). it's the same as if you were to double click the join line in design view and change the join properties from "display all from this table and only that table that match" or "display all from that table and only this table that match".

edit: (maybe) union queries are NOT my forte!

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.

can you post the SQL again as it is now (with the working date but not aggregate clicks)?

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 Gold].[Mailing Name]=[US Platinum].[Mailing Name]
GROUP BY [US Platinum].[Mailing Date]
UNION ALL SELECT [US Platinum].[Mailing Date], Sum([US Platinum].[Unique click]+[US Gold].[Unique Click]+[US GameClub].[Unique Click]) AS AggregateClicks
FROM ([US GameClub] RIGHT JOIN [US Gold] ON [US GameClub].[Mailing Name]=[US Gold].[Mailing Name]) RIGHT JOIN [US Platinum] ON [US Gold].[Mailing Name]=[US Platinum].[Mailing Name]
GROUP BY [US Platinum].[Mailing Date];

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 date/time...I can't get rid of the time...do you know how?

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

something like Format(Date, "yyyy/mmm/dd") ? check in access help the proper way to format the date for datepart only... Oh! DatePart()?

(the best way to check how to get this in SQL: copy just one of your SELECT statements into a new query view, and go into design and do the format() there, then switch to SQL view, then copy n paste back into your union query).

i have done a date thingy in the past... gimme a moment to dig this back up again...

Format([DateField],"ddmmmyyyy")

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 a syntax error..does it go somewhere else?

ok, first, you need to change the "[DateField]" to the name of you field with your date in it. second, if you're going to write the SQL from scratch, i think it's supposed to be more like

format([Mailing Date], "ddmmmyyyyy") AS [Mailing Date Formatted] or something.... i'm not sure exactly... which is why i suggested doing it in design view of just a select query...

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!

yes, union queries are too complex and cannot view in design, but the SELECT part should be viewable in design view. create a new query, go to SQL view and paste in just the first select part, i.e.


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 Gold].[Mailing Name]=[US Platinum].[Mailing Name]
GROUP BY [US Platinum].[Mailing Date]
then chose Design View (this query doesn't look like a challenge to me, it should view ok in design). once you change the format of the field [mailing date] to frmtMailingDate: format([mailing date],"dd/mm/yyyy") it should give you something like this back in SQL view:


SELECT (format([US Platinum].[mailing date],"dd/mm/yyyy")) AS frmtMailingDate, 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 Gold].[Mailing Name]=[US Platinum].[Mailing Name]
GROUP BY [frmtMailingDate]
but just check that this works - i'm just doing guesstimation here...

my point with switching between SQL and Design view is just that (especially me and when i learn) i don't know how to properly write it in SQL-speak, so if i make a query in Design View first, i can then view it in SQL View and see how to properly do it.

if you already know how to properly do SQL speak this is a superflous step, but as i'm sure we've both demonstrated in these posts, neither of us are super-familiar with SQL-speak ;)

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.

i have already, a couple of times. but a picture speaks a thousand words... (see attachment) ;)

in this query (real-world examples from one of my work databases), i had also wanted to change the format to uppercase, hence the UCase() wrap. you can leave this out if you don't want it. also, if you want to put backslashes in, i think you'll have to put them in such:

dd\/mmm\/yyyy

or maybe it won't matter.... play around with it if it doesn't work.