anyone see where i messed up this query?

[pbaldy]

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.

 

[lmp101010]

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];

 

[wiklendt]

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!

 

[lmp101010]

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.

 

[wiklendt]

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

 

[lmp101010]

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];

 

[lmp101010]

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?

 

[lmp101010]

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

 

[wiklendt]

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...

 

[wiklendt]

Format([DateField],"ddmmmyyyy")

 

[lmp101010]

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?

 

[wiklendt]

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...

 

[lmp101010]

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!

 

[wiklendt]

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 [COLOR=Red](format([US Platinum].[mailing date],"dd/mm/yyyy")) AS frmtMailingDate[/COLOR], 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 [COLOR=Red][frmtMailingDate][/COLOR]

but just check that this works - i'm just doing guesstimation here...

 

[wiklendt]

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

 

[lmp101010]

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.

 

[wiklendt]

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.