> Now that you've posted what your data looks like (5 fields named Cesales,
> Ctsales, Medsales, Subsales and Dscpsales), it would seem that all you need
> is
>
> SELECT "Ce" As Location, Cesales As Sales
> FROM MyTable
> WHERE Cesales IS NOT NULL
> UNION
> SELECT "Ct", Ctsales
> FROM MyTable
> WHERE Ctsales IS NOT NULL
> UNION
> SELECT "Med", Medsales
> FROM MyTable
> WHERE Medsales IS NOT NULL
> UNION
> SELECT "Sub", Subsales
> FROM MyTable
> WHERE Subsales IS NOT NULL
> UNION
> SELECT "Dscp", Dscpsales
> FROM MyTable
> WHERE Dscpsales IS NOT NULL
>
> (Note that it's not actually necessary to include the As Location and As
> Sales on each of the subselects: the field names are determined from the
> first subselect only.)
>
> The reason I'd put Field1, Field2 in the previous response was to handle the
> case where you have other fields in the table that don't represent sales
> information. For instance, if your table looked like
>
> SalesRep Qtr Cesales Ctsales Medsales Subsales Dscpsales
> Jones 2007/Q3 $9,695,491.64 $0.00 $0.00 $0.00 $9,695,491.64
> Smith 2007/Q3 $9,595,730.03 $9,052.08 $7,393.06 $0.00 $9,612,175.17
> Brown 2007/Q3 $9,527,275.82 $3,608,971.60 $331,126.73 $997,414.50
> $14,464,788.65
> Jones 2008/Q1 $10,693,231.63 $0.00 $0.00 $0.00 $7,123,333.21
> Smith 2008/Q1 $9,695,491.64 $0.00 $0.00 $0.00 $9,695,491.64
>
> and so on, and you wanted
>
> SalesRep Qtr Location Sales
>
> returned, you'd use
>
> SELECT SalesRep, Qtr, "Ce" As Location, Cesales As Sales
> FROM MyTable
> WHERE Cesales IS NOT NULL
> UNION
> SELECT SalesRep, Qtr, "Ct", Ctsales
> FROM MyTable
> WHERE Ctsales IS NOT NULL
> UNION
> SELECT SalesRep, Qtr, "Med", Medsales
> FROM MyTable
> WHERE Medsales IS NOT NULL
> UNION
> SELECT SalesRep, Qtr, "Sub", Subsales
> FROM MyTable
> WHERE Subsales IS NOT NULL
> UNION
> SELECT SalesRep, Qtr, "Dscp", Dscpsales
> FROM MyTable
> WHERE Dscpsales IS NOT NULL
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "jackie" discussions.microsoft.com> wrote in message
> news:9485CB51-2F7F-4BB3-938F-649C8637BB4D@microsoft.com...

>> It Worked!! Thanks so much.
>> At first I thought it didn't work, but when I checked the data, it had
>> worked.
>>
>> It asked for a parameter for field1 and field2, so I entered field names
>> "Location" for field 1 and "sales" for field 2. I'm not sure if that was
>> correct. I didn't actually execute the query yet, I just ran it in
>> datasheet
>> view.
>>
>> The output looks like this:
>> Field1 Field2 Location Sales
>> location sales CT $6,320,332.49
>> location sales CT $6,370,373.26
>>
>> If I look down the columns, the sales figures all line up with the correct
>> locations.
>> Did I enter the correct information in field one and field 2? Thanks.
>>
>>
>>
>> "Douglas J. Steele" wrote:
>>

>>> Looking at Allen Browne's list of Problem names and reserved words in
>>> Access
>>> at http://www.allenbrowne.com/AppIssueBadWord.html I see that State isn't
>>> a
>>> good choice for a field name. Let's use Location instead (which, I now
>>> see,
>>> is what you'd originally suggested anyhow).
>>>
>>> I'm assuming you have a table named ExistingTable. What I've proposed is
>>> SQL
>>> for a union query that will transform ExistingTable into the format you
>>> want. I'm expecting that you'd type that SQL as is (replacing
>>> ExistingTable
>>> with the name of your actual table) into the SQL view of a query, and it
>>> would run. Of course, if you have additional fields in the table, you'll
>>> want to include those additional fields, so that it would look something
>>> like:
>>>
>>> SELECT Field1, Field2, "FL" As Location, FLsales As Sales
>>> FROM ExistingTable
>>> WHERE FLsales IS NOT NULL
>>> UNION
>>> SELECT Field1, Field2, "NY" As Location, NYsales As Sales
>>> FROM ExistingTable
>>> WHERE NYsales IS NOT NULL
>>> UNION
>>> SELECT Field1, Field2, "CA" As Location, CAsales As Sales
>>> FROM ExistingTable
>>> WHERE CAsales IS NOT NULL
>>> UNION
>>> SELECT Field1, Field2, "NJ" As Location, NJsales As Sales
>>> FROM ExistingTable
>>> WHERE NJsales IS NOT NULL
>>>
>>> I don't see any syntax error in there, and it has nothing to do with the
>>> fact that there's no field called "state" (now Location) and
>>> no value called "CA" or "NJ".`
>>>
>>> If it still doesn't work for you, show me the actual SQL you're trying to
>>> run.
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "jackie" discussions.microsoft.com> wrote in message
>>> news:D662B3B0-267C-4925-BDA6-35FC7A89C368@microsoft.com...

>>>> Hi,
>>>> I tried it and I get the following error:
>>>> "The Select statement includes a reserved word or an argument name that
>>>> is
>>>> mispelled or missing, or the Punctuation is incorrect"
>>>>
>>>> Is this a syntax error or is it because there's no field called "state"
>>>> and
>>>> no value called "CA" or "NJ". The only field that exists is CAsales or
>>>> NJsales. Thanks.
>>>>
>>>>
>>>>
>>>>
>>>> "Douglas J. Steele" wrote:
>>>>

>>>>> Try a Union query:
>>>>>
>>>>>
>>>>> "jackie" discussions.microsoft.com> wrote in message
>>>>> news:B465EE34-9B1C-4375-8067-C73E545D49D1@microsoft.com...

>>>>>>I have a database that was handed off to me. It shows the sales data
>>>>>>for
>>>>>>each
>>>>>> state, but instead of having a location field and a sales field,
>>>>>> there
>>>>>> is
>>>>>> just one field that is all inclusive. The table looks like this:
>>>>>> FLsales
>>>>>> NYsales
>>>>>> CAsales
>>>>>> NJsales
>>>>>> I need the table to look like this.
>>>>>> Location (Florida, New York, Calif.)
>>>>>> Sales
>>>>>> Then I could build a query where the user could enter a location and
>>>>>> get
>>>>>> the
>>>>>> sales data. (I am using query by form.)
>>>>>>
>>>>>> How can I move NYsales to two fields, one called "sales" and one
>>>>>> called
>>>>>> 'location' and still keep the sales data for that row connected to
>>>>>> the
>>>>>> New
>>>>>> York location. I have about 15000 rows. Thank you.

>>>
>>>
>>>

>
>
>