automate a make table query

[Guest]

Is there anyway to automate this make table query? I need it to check the
linked Excel table and if there is a new date field that isn't listed then
add it to the list when I hit the make query icon? For example, if my linked
excel sheet has a new column 9/1 then I would like to write a query that can
add the new date/s. I have the linked sheet as an Excel table in Access.


SELECT [Port History_3].Area, [Port History_3].Headend, [Port
History_3].[Element Name], [Port History_3].DOCSIS, [Port History_3].Subs,
[Port History_3].Sub, [Port History_3].[#8/4/2007#], [Port
History_3].[#8/18/2007#], [Port History_3].[#8/25/2007#], [Port
History_3].[#9/1/2007#], [Port History_3].NODE, [Port History_3].[Optical TX]
INTO _HSIRouterImport
FROM [Port History] AS [Port History_3];

 

[Guest]

If I wrote an Access vba script to find the number of date fields could I
write a SQL make query that could use that as input?
thanks,

 

[Jeff Boyce]

Janis

It sounds like you want Access to have a table that looks like your Excel
spreadsheet.

Bad idea! If you want to get good use of the relationally-oriented features
and functions that Access offers, you can't feed it 'sheet data.

Another approach to dealing with Excel data is to "map" between the raw data
Excel can provide and a more-permanent, well-normalized relational data
structure that lets Access work better.

Or have I over-interpreted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for your response. Yes it is recreating a flat file in Access. I need
to create an Excel report with all the weeks in the current year. The update
sheets come from a data source we don't have access to. The update sheets
only have the last 6 weeks. We need to create an excel report with the
current year all the weeks or at least the last 6 months. Since the update
sheets come every week I could do it in Excel and Vba however there are some
other things that get done to the report. There is a chart showing all the
data points. There is another smilar excel report that gets compared to
this one. It is a lengthy process which I think would benefit from being in
Access.

Janis

It sounds like you want Access to have a table that looks like your Excel
spreadsheet.

Bad idea! If you want to get good use of the relationally-oriented features
and functions that Access offers, you can't feed it 'sheet data.

Another approach to dealing with Excel data is to "map" between the raw data
Excel can provide and a more-permanent, well-normalized relational data
structure that lets Access work better.

Or have I over-interpreted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there anyway to automate this make table query? I need it to check the
linked Excel table and if there is a new date field that isn't listed
then
add it to the list when I hit the make query icon? For example, if my
linked
excel sheet has a new column 9/1 then I would like to write a query that
can
add the new date/s. I have the linked sheet as an Excel table in Access.


SELECT [Port History_3].Area, [Port History_3].Headend, [Port
History_3].[Element Name], [Port History_3].DOCSIS, [Port History_3].Subs,
[Port History_3].Sub, [Port History_3].[#8/4/2007#], [Port
History_3].[#8/18/2007#], [Port History_3].[#8/25/2007#], [Port
History_3].[#9/1/2007#], [Port History_3].NODE, [Port History_3].[Optical
TX]
INTO _HSIRouterImport
FROM [Port History] AS [Port History_3];

 

[Guest]

Someone suggested going retrieving the new columns cell by cell. Is this the
only way?
thanks,

Janis

It sounds like you want Access to have a table that looks like your Excel
spreadsheet.

Bad idea! If you want to get good use of the relationally-oriented features
and functions that Access offers, you can't feed it 'sheet data.

Another approach to dealing with Excel data is to "map" between the raw data
Excel can provide and a more-permanent, well-normalized relational data
structure that lets Access work better.

Or have I over-interpreted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there anyway to automate this make table query? I need it to check the
linked Excel table and if there is a new date field that isn't listed
then
add it to the list when I hit the make query icon? For example, if my
linked
excel sheet has a new column 9/1 then I would like to write a query that
can
add the new date/s. I have the linked sheet as an Excel table in Access.


SELECT [Port History_3].Area, [Port History_3].Headend, [Port
History_3].[Element Name], [Port History_3].DOCSIS, [Port History_3].Subs,
[Port History_3].Sub, [Port History_3].[#8/4/2007#], [Port
History_3].[#8/18/2007#], [Port History_3].[#8/25/2007#], [Port
History_3].[#9/1/2007#], [Port History_3].NODE, [Port History_3].[Optical
TX]
INTO _HSIRouterImport
FROM [Port History] AS [Port History_3];

 

[Jeff Boyce]

Janis

It's a common (mis-)perception that you have to have a "matching" table
structure in Access in order to generate a report, or to export data to
Excel, or ...

Not! To get the best use of Access' tools, first normalize your data. When
you have relationally-oriented data tables in Access, you use queries to
join the data back together in "flat" views suitable for reports, exporting
to Excel, etc.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your response. Yes it is recreating a flat file in Access. I
need
to create an Excel report with all the weeks in the current year. The
update
sheets come from a data source we don't have access to. The update sheets
only have the last 6 weeks. We need to create an excel report with the
current year all the weeks or at least the last 6 months. Since the
update
sheets come every week I could do it in Excel and Vba however there are
some
other things that get done to the report. There is a chart showing all
the
data points. There is another smilar excel report that gets compared to
this one. It is a lengthy process which I think would benefit from being
in
Access.

Janis

It sounds like you want Access to have a table that looks like your Excel
spreadsheet.

Bad idea! If you want to get good use of the relationally-oriented
features
and functions that Access offers, you can't feed it 'sheet data.

Another approach to dealing with Excel data is to "map" between the raw
data
Excel can provide and a more-permanent, well-normalized relational data
structure that lets Access work better.

Or have I over-interpreted?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there anyway to automate this make table query? I need it to check
the
linked Excel table and if there is a new date field that isn't listed
then
add it to the list when I hit the make query icon? For example, if my
linked
excel sheet has a new column 9/1 then I would like to write a query
that
can
add the new date/s. I have the linked sheet as an Excel table in
Access.


SELECT [Port History_3].Area, [Port History_3].Headend, [Port
History_3].[Element Name], [Port History_3].DOCSIS, [Port
History_3].Subs,
[Port History_3].Sub, [Port History_3].[#8/4/2007#], [Port
History_3].[#8/18/2007#], [Port History_3].[#8/25/2007#], [Port
History_3].[#9/1/2007#], [Port History_3].NODE, [Port
History_3].[Optical
TX]
INTO _HSIRouterImport
FROM [Port History] AS [Port History_3];