query not very efficient

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in which each department is set up as a
column and so I have 12-15 columns in my table. I am
trying to decrease the number of columns and am
storing "department name" as a column. I have the
following code working...

INSERT INTO [Data] ( Acc, dept, Amount )
SELECT [Data1].[AC-ID], "Acct" AS Expr1, [Data1].[ACCT]
FROM [Data1];

I was wondering if there is a simpler way of converting
all the columns instead of running similar code as above
for all the columns. Can I replace "Acct" (string vlaue)
and [ACCT] (heading of a clumn) with variables?

Thanks
 
Is there any reason wy you have separate columns for each department instead
of having a table for departments (e.g. tblDepartments) which you can then
link to the related entity?


Jamie
 
Unless you switch to a relational design with one column to identify the
department and other columns for whatever, you will continue to have
problems and will have to write code to solve those problems.
With a relational design no code is needed.

Once this is done you can run 12- 15 append queries to update the correctly
designed table.
I have a table in which each department is set up as a
column and so I have 12-15 columns in my table. I am
trying to decrease the number of columns and am
storing "department name" as a column. I have the
following code working...

INSERT INTO [Data] ( Acc, dept, Amount )
SELECT [Data1].[AC-ID], "Acct" AS Expr1, [Data1].[ACCT]
FROM [Data1];

I was wondering if there is a simpler way of converting
all the columns instead of running similar code as above
for all the columns. Can I replace "Acct" (string vlaue)
and [ACCT] (heading of a clumn) with variables?

Thanks
Click to expand...
Click to expand...
 
I have a table in which each department is set up as a
column and so I have 12-15 columns in my table. I am
trying to decrease the number of columns and am
storing "department name" as a column. I have the
following code working...

INSERT INTO [Data] ( Acc, dept, Amount )
SELECT [Data1].[AC-ID], "Acct" AS Expr1, [Data1].[ACCT]
FROM [Data1];

I was wondering if there is a simpler way of converting
all the columns instead of running similar code as above
for all the columns. Can I replace "Acct" (string vlaue)
and [ACCT] (heading of a clumn) with variables?
Click to expand...


You could write a VBA procedure to do that, but for a
one-time conversion, it's faster and easier to just do it 15
times right in the query design window.

You may also want to use a WHERE clause to only convert
records that have a value in the specified dept column.
 
Back
Top