re-post of query

Guest · May 12, 2004

Hi (original query posted 4/22/2004 2.33PM

I have tried to create the union query as per your last post. I'm not having any joy at the minute (i'm positive its something that im doing!) as i keep getting a syntax in FROM clause. I'm sure i followed your instructions to the letter but i'm still doing something wrong
Below is what i have written.

SELECT TOP 10 productcode, Count(1
FROM
(UNION SELECT column7 AS productcode FROM Garment Tracking Details
UNION SELECT column8 FROM Garment Tracking Details
UNION SELECT column9 FROM Garment Tracking Details
UNION SELECT column10 FROM Garment Tracking Details
GROUP BY productcod
ORDER BY Count(1) DESC

The way my table is set out is as follows:- ( i know the table is not normalised, i inherited it!, i will eventually change it) I would put the results of Prod 1-Prod 8 in one column. I would also like to put in a date paremeter if poss

GMTNo Date Brand StoreNo Storetelno Storecontact Prod 1 Prod 2 Prod 3 Prod 4 Prod 5 Prod 6 Prod 7 Prod
eg 1 12/5/04 Cst 9985 Julie 211116089014 785890458616 etc etc

I would be grateful for any help you can offer

Thanks as alway

Caro

John Vinson · May 12, 2004

Hi (original query posted 4/22/2004 2.33PM)

I have tried to create the union query as per your last post. I'm not having any joy at the minute (i'm positive its something that im doing!) as i keep getting a syntax in FROM clause. I'm sure i followed your instructions to the letter but i'm still doing something wrong.
Below is what i have written.

SELECT TOP 10 productcode, Count(1)
FROM
(UNION SELECT column7 AS productcode FROM Garment Tracking Details
UNION SELECT column8 FROM Garment Tracking Details
UNION SELECT column9 FROM Garment Tracking Details
UNION SELECT column10 FROM Garment Tracking Details )
GROUP BY productcode
ORDER BY Count(1) DESC;

Since your table name contains blanks, you MUST enclose the name in
[square brackets] - otherwise it will see the blank after Garment as
the end of the table name, and become perplexed about what to do with
the words Tracking and Details.

In addition, you start the Subquery with a UNION - that's starting in
the middle.

Try

SELECT TOP 10 productcode, Count(1)
FROM
(SELECT column7 AS productcode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details]
UNION SELECT column9 FROM [Garment Tracking Details]
UNION SELECT column10 FROM [Garment Tracking Details] )
GROUP BY productcode
ORDER BY Count(1) DESC;

I'm not totally certain this will work - you may need to create and
save the UNION query, and then base your TOTALS query on that saved
query.

Guest · May 13, 2004

Hi Joh

Thank you for replying. Unfortunately I have re-written the query and i am still getting the syntax error in FROM clause

Here's what i have writte

SELECT TOP 10 productcode, Count (1
FRO
(SELECT column7 AS productcode FROM [Garment Tracking Details
UNION SELECT column8 FROM [Garment Tracking Details
UNION SELECT column9 FROM [Garment Tracking Details
UNION SELECT column10 FROM [Garment Tracking Details]
GROUP BY productcod
ORDER BY Count (1) DESC

Thanks for any help or advice you can offer, I will get this right if its the last thing i do!

Carol

John Vinson · May 13, 2004

Hi John

Thank you for replying. Unfortunately I have re-written the query and i am still getting the syntax error in FROM clause.

Here's what i have written

SELECT TOP 10 productcode, Count (1)
FROM
(SELECT column7 AS productcode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details]
UNION SELECT column9 FROM [Garment Tracking Details]
UNION SELECT column10 FROM [Garment Tracking Details])
GROUP BY productcode
ORDER BY Count (1) DESC;

Thanks for any help or advice you can offer, I will get this right if its the last thing i do! Carol

I guess you'll need to split this into two Queries.

Save

SELECT column7 AS productcode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details]
UNION SELECT column9 FROM [Garment Tracking Details]
UNION SELECT column10 FROM [Garment Tracking Details]

as uniAllDetails; then create a second query based on that saved
query:

SELECT TOP 10 Productcode, Count(productcode)
FROM uniAllDetails
ORDER BY Count([productcode]) Desc;

John Spencer (MVP) · May 14, 2004

Pardon me for jumping in. But in addition to all John Vinson has noted, I
think you may have to Change "Union" to "Union All". Just "Union" will
aggregate (condense, combine) all like records into one. So you will probably
get all the product codes returned since the union query as written will return
ONE row for each product code.

John said:
Hi John

Thank you for replying. Unfortunately I have re-written the query and i am still getting the syntax error in FROM clause.

Here's what i have written

SELECT TOP 10 productcode, Count (1)
FROM
(SELECT column7 AS productcode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details]
UNION SELECT column9 FROM [Garment Tracking Details]
UNION SELECT column10 FROM [Garment Tracking Details])
GROUP BY productcode
ORDER BY Count (1) DESC;

Thanks for any help or advice you can offer, I will get this right if its the last thing i do! Carol

Click to expand...

I guess you'll need to split this into two Queries.

Save

SELECT column7 AS productcode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details]
UNION SELECT column9 FROM [Garment Tracking Details]
UNION SELECT column10 FROM [Garment Tracking Details]

as uniAllDetails; then create a second query based on that saved
query:

SELECT TOP 10 Productcode, Count(productcode)
FROM uniAllDetails
ORDER BY Count([productcode]) Desc;

Guest · May 14, 2004

Hi Al

I am so close now..... its still not working but im fairly sure that it must be something simple that ive missed

i've written UniAllDetails as below:

SELECT column7 AS productcode FROM [Garment Tracking Details
UNION SELECT column8 FROM [Garment Tracking Details
UNION SELECT column9 FROM [Garment Tracking Details
UNION SELECT column10 FROM [Garment Tracking Details
UNION SELECT column11 FROM [Garment Tracking Details
UNION SELECT column12 FROM [Garment Tracking Details]

When i run this query it is asking me for parameters for each of the columns....

I've then written a second query saved as UniAllCount which is as follows:

SELECT TOP 10 Productcode, Count (productcode
FROM uniAllDetail
ORDER BY Count ([productcode]) DESC

When i try to run this query is comes up with the following error message

You tried to execute a query that doesnt include the specified expression 'Productcode' as part of an aggregate function

Any ideas....sorry to be such a pain in the ***

Would it be anything to do with the fact that the columns are actually called prod 1, prod 2, prod 3 etc etc...probably completely off the mark!!

Thanks agai

Carol x

John Spencer (MVP) · May 15, 2004

You have to use YOUR column names. So if they are Prod 1 etc, you need to
change it to

SELECT [Prod 1] As ProductCode FROM [Garment Tracking Details]
UNION SELECT column8 FROM [Garment Tracking Details] ...

Your UniAllCount should have a GROUP BY statement in it

SELECT TOP 10 Productcode, Count (productcode)
FROM uniAllDetails
GROUP BY ProductCode
ORDER BY Count ([productcode]) DESC

As I mentioned elsewhere in the thread, I believe you will need to change
uniAllDetails and replace UNION with UNION ALL. Otherwise your count is going
to be 1 for every product code and you will get all the product codes returned.

Guest · May 16, 2004

Hi Joh

Thank you for your help & patience..

I will put in the correct info when i go back to work on monday

If it doesnt work this time I think i ought to file it in the dustbin before i drive you all mad

Thanks agai

Carol

Guest · May 17, 2004

Hi Joh

It worked a treat thank you

. For fear of getting screamed at, please could i ask just one more question

I need to put in a date paremeter and also a brand paremeter. I need to put in a range of two dates and i also need to able to specify which brand to run the query for

The date is entered into column2 and the choice of brand is in column3
Id be grateful for any advice you can offer and promise faithfully that this is the last question :

Carol

Guest · May 17, 2004

dont worry - i figured it out horray!!!

John Spencer (MVP) · May 18, 2004

So, at this point you do NOT need further assistance with adding in the query
parameters. Is that correct?

I hope it is, but if it isn't post back and we'll plod forward (or sideways, or
backwards) together.

Guest · May 18, 2004

Hi Joh

am I making you go grey!!! - dont worry, i managed to solve it

appreciate your offer of help though, thank you!

John Spencer (MVP) · May 19, 2004

Grey, I hope not. I'm mostly pink up top - lack of hair. Please, keep asking
questions, but don't worry - I won't pull out any of my hair.

Worst thing that will happen is I will choose to ignore your problem or I won't
even see it.

Good luck and keep on working at it. It gets easier to do the simpler things
and then you try doing the really complex stuff.

Guest · May 19, 2004

Hi Joh

i think that one was complicated enough...half of it is still gobble de gook to me

I appreciate your time though, without help from people such as yourself us "learners" wouldnt be able to improve. You never know, one day i might be able to help you!!

re-post of query

Guest

John Vinson

Guest

John Vinson

John Spencer (MVP)

Guest

John Spencer (MVP)

Guest

Guest

Guest

John Spencer (MVP)

Guest

John Spencer (MVP)

Guest