How to change table structure and make 1 field into 2

[jackie]

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.

 

[Douglas J. Steele]

Try a Union query:

SELECT "FL" As State, FLsales As Sales
FROM ExistingTable
WHERE FLsales IS NOT NULL
UNION
SELECT "NY" As State, NYsales As Sales
FROM ExistingTable
WHERE NYsales IS NOT NULL
UNION
SELECT "CA" As State, CAsales As Sales
FROM ExistingTable
WHERE CAsales IS NOT NULL
UNION
SELECT "NJ" As State, NJsales As Sales
FROM ExistingTable
WHERE NJsales IS NOT NULL

 

[jackie]

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"
Here's the code again:
SELECT "FL" As State, FLsales As Sales

FROM ExistingTable
WHERE FLsales IS NOT NULL
UNION
SELECT "NY" As State, NYsales As Sales
FROM ExistingTable
WHERE NYsales IS NOT NULL
UNION
SELECT "CA" As State, CAsales As Sales
FROM ExistingTable
WHERE CAsales IS NOT NULL
UNION
SELECT "NJ" As State, NJsales As Sales
FROM ExistingTable
WHERE NJsales IS NOT NULL

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]

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.

 

[jackie]

Thanks for responding Douglas.
Here's what my data looks like:
Cesales Ctsales Medsales Subsales Dscpsales
$9,695,491.64 $0.00 $0.00 $0.00 $9,695,491.64
$9,595,730.03 $9,052.08 $7,393.06 $0.00 $9,612,175.17
$9,527,275.82 $3,608,971.60 $331,126.73 $997,414.50 $14,464,788.65

 

[jackie]

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]

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

 

[jackie]

That is what I want. I have some other fields in the table and I thought I
would just be able to link the old table and the new table and get the other
fields. But I have'nt' been able to. The sales dollar field just repeats. I'm
going to try what you just suggested. I'll let you know the outcome. Thanks.

 

[jackie]

Douglas,
It worked. It's a beautiful thing. I used the last query you showed me to
bring in all of the fields from the old table. Thank you very much.