Append Query

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hi:

For the below qry. for every new month/period I have to go in the Design
mode and add the new fileds to be appended to the correspeonding ones; for
example in December = 6, I will do the modification.

NSERT INTO bff ( FFiscal_Year, KAM, CG, CGNAME, PG, PNAME, 7, 8, 9, 10, 11,
GSCAD1, GSCAD2, GSCAD3, GSCAD4, GSCAD5 )
SELECT [qry-for-upd-2009].[2009CTforUpdtCases].FISCAL_YEAR,
[qry-for-upd-2009].[2009CTforUpdtCases].KAM,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].[1],
[qry-for-upd-2009].[2009CTforUpdtCases].[2],
[qry-for-upd-2009].[2009CTforUpdtCases].[3],
[qry-for-upd-2009].[2009CTforUpdtCases].[4],
[qry-for-upd-2009].[2009CTforUpdtCases].[5],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[1],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[2],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[3],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[4],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[5]
FROM [qry-for-upd-2009];

Can you please tell me if (probably not) there is a 'way' to get this
automatically?

Thank you,
Dan
 
yeah rebuild your database to not seperate out time periods just have
a date/time column which will contain the date and time so you can use
that to filter out years or months or days

Regards
Kelvan
 
Hi:

For the below qry. for every new month/period I have to go in the Design
mode and add the new fileds to be appended to the correspeonding ones; for
example in December = 6, I will do the modification.

NSERT INTO bff ( FFiscal_Year, KAM, CG, CGNAME, PG, PNAME, 7, 8, 9, 10, 11,
GSCAD1, GSCAD2, GSCAD3, GSCAD4, GSCAD5 )
SELECT [qry-for-upd-2009].[2009CTforUpdtCases].FISCAL_YEAR,
[qry-for-upd-2009].[2009CTforUpdtCases].KAM,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].[1],
[qry-for-upd-2009].[2009CTforUpdtCases].[2],
[qry-for-upd-2009].[2009CTforUpdtCases].[3],
[qry-for-upd-2009].[2009CTforUpdtCases].[4],
[qry-for-upd-2009].[2009CTforUpdtCases].[5],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[1],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[2],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[3],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[4],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[5]
FROM [qry-for-upd-2009];

Can you please tell me if (probably not) there is a 'way' to get this
automatically?
Click to expand...

Your problem stems from the fact that your database design IS WRONG. You're
storing data - fiscal months, I gather - in fieldnames. You should instead
consider having two tables in a one to many relationship.
 
Thanks John/Lord!

This is the way I have inherited it...

Dan

John W. Vinson said:
Hi:

For the below qry. for every new month/period I have to go in the Design
mode and add the new fileds to be appended to the correspeonding ones; for
example in December = 6, I will do the modification.

NSERT INTO bff ( FFiscal_Year, KAM, CG, CGNAME, PG, PNAME, 7, 8, 9, 10, 11,
GSCAD1, GSCAD2, GSCAD3, GSCAD4, GSCAD5 )
SELECT [qry-for-upd-2009].[2009CTforUpdtCases].FISCAL_YEAR,
[qry-for-upd-2009].[2009CTforUpdtCases].KAM,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].CUSTOMER_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NO,
[qry-for-upd-2009].[2009CTforUpdtCases].PROMO_GROUP_NAME,
[qry-for-upd-2009].[2009CTforUpdtCases].[1],
[qry-for-upd-2009].[2009CTforUpdtCases].[2],
[qry-for-upd-2009].[2009CTforUpdtCases].[3],
[qry-for-upd-2009].[2009CTforUpdtCases].[4],
[qry-for-upd-2009].[2009CTforUpdtCases].[5],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[1],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[2],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[3],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[4],
[qry-for-upd-2009].[2009CTforUpdtGrossSsles].[5]
FROM [qry-for-upd-2009];

Can you please tell me if (probably not) there is a 'way' to get this
automatically?
Click to expand...

Your problem stems from the fact that your database design IS WRONG. You're
storing data - fiscal months, I gather - in fieldnames. You should instead
consider having two tables in a one to many relationship.
Click to expand...
 
Thanks John/Lord!

This is the way I have inherited it...
Click to expand...

You can use a "Normalizing Union Query" to extract the data from this
spreadsheetish table into a properly normalized one; if you'll be working with
the data long term it's certainly a good idea. Post back with your current
fieldnames, datatypes and meanings and we can help get the data restructured
for you.
 
i suggest rebuilding your data to support a good database structure.
basically there is no way to automatically do what you want as
databases are not designed to work that way. you can then use append
queries to add the data to the new table structure and tweek any forms
you use. you have found one of the problems with having a database
set up like it is.

as for who built it in the first place they should simply be shot ^_^
for not having the forsight to realise that 20 tables all doing the
same thign is bad. as far as i am conserned if you build you DB
properly you should never need to add a new table down the line for
any reason. this is not 100% but it is the theroy

Regards
Kelvan
 
Back
Top