Can I query where the table name will change?

  • Thread starter Thread starter B Nieds
  • Start date Start date
B

B Nieds

Hi:

At the office we use an inventory system. The system uses dBase files. I
have created my Access database with links to the inventory system and my
sample brings back the data that I want. The problem I am having is that two
of the file names will change. How can I tell Access the name of the file I
want?

Basically I will always work with a group of three files. I use file one to
get a list of files names which have the data I want. These data files
always come in pairs. So I query file one (this file name is always the
same) to get a list of file names and then I want to pass these names to a
query which will retreive the data I want from the files.

Any suggestions?

Thanks
Bill
 
You have an attached table, and you want to point to a different source file
that has exactly the same data structure?

If so, set the Connect property of the TableDef, and remember to
RefreshLink.
 
When do you want to do this?
In code that is called by your AutoExec macro, or the Open event of your
startup form? Or when the user clicks a button to switch data sources?
 
Sorry, i just link up an excel data file to the "Access" for using the
function "select from". but i want to change the excel data file frequently
in order to generate same sorts of data from different excel file.
 
If you do not want to do this programmatically, you might be able to use the
Linked Table manager under Tools | Database Utilities.

Alternatively, could delete your linked table, and link again: File | Get
External | Link. Then rename the linked table (in the Database window) if
necessary so that it has the name used in your query.
 
i've found the right place.
thanks Allen


Allen Browne said:
If you do not want to do this programmatically, you might be able to use the
Linked Table manager under Tools | Database Utilities.

Alternatively, could delete your linked table, and link again: File | Get
External | Link. Then rename the linked table (in the Database window) if
necessary so that it has the name used in your query.
Click to expand...
 
Hi:

I have been playing with this suggestion but the final solution still eludes
me and the reason is probably that I do not know how to modify the query. At
any rate here is what I have done.

On the network I have a folder called Inventory on drive X which has a file
(the name does not change) called MasterList. I created a query to get a
list of file names I want. For simplicity lets say it returns three file
names (Sales15, Sales21, and Sales32) all of which have the same file
structure but represents data from three departments. The number in the file
name (15, 21, and 32) changes depending on what work was done for the day
and the number of Sales files differs each day. What I want is a report
which will summarize the data from the three files.

So I have created a query to read the Sales file and append the data to a
report file from which I can run the desired report. Manually I can change
the qSales query to use the second and then third files and then the report
runs fine. I have been avoiding the write code option because I lack
experience in coding so I tried to pass a parameter from a form to the query
but my query fails with missing table.

Is there a simple solution to this problem or do I need to learn to write
code?

Thanks
Bill

You have an attached table, and you want to point to a different source file
that has exactly the same data structure?
Click to expand...
 
Dear Bill:

Here's a suggestion for a completely different solution.

Link to all 3 tables. Create a query that is a UNION of the 3 tables,
adding a new column I'll call Source. For the 3 tables, put 1, 2, or
3 in the Source column.

Then, rather than having to "switch" tables, you can simply filter to
1, 2, or 3 to access the data in each table independently.

If you need to insert new rows or update, you would not be able to do
it this way. But it would make querying the data very simple. You
could even create combinations of tables this way.

The UNION query would look like:

SELECT 1 AS Source, * FROM Sales15
UNION ALL
SELECT 2 AS Srouce, * FROM Sales21
UNION ALL
SELECT 3 AS Source, * FROM Sales32

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom:

Thanks for the response. How does the UNION allow me to change the source
names? Today I know that the three tables (Sales15, Sales21, and Sales32)
exist but tomorrow the names could be Sales5, Sales2, and Sales3 so how do I
change the UNION query so it knows to use then new names?

Thanks
Bill
 
Dear Bill:

Are you constantly creating more and more SalesXX tables, or do they
already exist. If they already exist, just put them all in the UNION,
then filter to the one(s) you want in each instance.

If you are constantly creating new tables then you could still use the
UNION approach, but you would have to generate the SQL for the query
dynamically to handle the current set of tables.

It would be so much better to have a good database design, in which
you have only one table, with a column in it that says whether it is
15, 21, or 32. This is really making things difficult for you,
perhaps unnecessarily. If these tables are constantly arriving (and
disappearing) from some external source, you could append all their
data to a single table, adding the Source column as I did in the UNION
query, and thus eliminate this difficulty.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom:

I wish I were the one creating the SalesXX files because that would give me
more control. The SalesXX are files created daily by the inventory system
and when I look at the reason why it makes sense to do it that way but it
does make my task more difficult. There is a table (called mstrlst) which
exists to tell me what file names I need and for which day. I then manually
change my second query to use these file names and the report gets created.
Manually is ok for now but I figured that there would have to be some
dynamic way to handle the changing of the file names. My question is there
some simple way without a lot changes on my part?

I have changed my query to use the UNION approach, it is fun to learn new
stuff, so thanks for that info.

Thanks
Bill
 
Dear Bill:

You could create a module that steps through the rows of mstrlst in a
recordset, creating each SELECT in the UNION query. It would build
the SQL of this query then, dynamically for each day.

If you put all the rows from each SalesXX into a single table, and
perhaps use the value of XX as the Source column, you could then JOIN
to the mstrlst table to find the rows for the current day's work. You
could also purge those that become obsolete.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom:

Thanks for the reply. I was afraid it would come down to writing some code
and this is an area I am totally unfamiliar with. Does what I want to do
require lots of code? I have a query I created to called qmstrlst which
returns the list of file names I will use for todays report. Can I use this
in the module somehow?

Sorry if these questions are not appropriate but I am not sure were to
start.


Thanks
Bill
Tom Ellison said:
Dear Bill:

You could create a module that steps through the rows of mstrlst in a
recordset, creating each SELECT in the UNION query. It would build
the SQL of this query then, dynamically for each day.

If you put all the rows from each SalesXX into a single table, and
perhaps use the value of XX as the Source column, you could then JOIN
to the mstrlst table to find the rows for the current day's work. You
could also purge those that become obsolete.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
---- snip -----------
 
Hi Tom:

Ok, now you have lost me. Now, I do not know if you lost me because I do not
understand your direction or if it is because I have not been clear enough
in describing the way the inventory files are created.

When you say "create a table to contain all the different SalesXX datasets"
do you mean "Since the SalesXX files already exist then do one massive
append all of the data to one massive collection file and the proceed to
extract and run the necessary report from this massive file?" in which case
I do not see a solution. The SalesXX files do not exist until the inventory
system creates them (based on whatever transaction occurred that day) and
that is why the XX keeps changing. So I do not know how to take a SalesXX
datasets (which does not yet exist) and append to a master list.

When I run my query qMstrlst it give me a list of SalesXX files created (and
still active) on which I can run reports. Currently I then use this list
(usually 3-5 file names) and manually change a second query (qBJinfo) to use
these new file names. The qBJinfo query (which uses your UNION and that made
my task much easier) to MakeTable (I never keep any old data) tBJinfo from
which all the reports are generated.

So, what am I not understanding in your answer?

Thanks
Bill

ps: My allergy to coding only stems from the fact that I have zero
experience with coding. I have read the help feature a couple of times, and
have looked at some examples, but I cannot seem to identify the pieces I
need to start. I like learning and when time permits I keep going deeper
into Access but so far coding escapes me.
 
Dear Bill:

See responses inserted below.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom:

Ok, now you have lost me. Now, I do not know if you lost me because I do not
understand your direction or if it is because I have not been clear enough
in describing the way the inventory files are created.

When you say "create a table to contain all the different SalesXX datasets"
do you mean "Since the SalesXX files already exist then do one massive
append all of the data to one massive collection file and the proceed to
extract and run the necessary report from this massive file?" in which case
I do not see a solution. The SalesXX files do not exist until the inventory
system creates them (based on whatever transaction occurred that day) and
that is why the XX keeps changing. So I do not know how to take a SalesXX
datasets (which does not yet exist) and append to a master list.
Click to expand...

I meant for you to append them as they arrive. Probably you would
also remove them as they become obsolete.
When I run my query qMstrlst it give me a list of SalesXX files created (and
still active) on which I can run reports. Currently I then use this list
(usually 3-5 file names) and manually change a second query (qBJinfo) to use
these new file names. The qBJinfo query (which uses your UNION and that made
my task much easier) to MakeTable (I never keep any old data) tBJinfo from
which all the reports are generated.
Click to expand...

An INNER JOIN between mstrlst and the combined Sales table would give
you only those XX portions desired.
 
Hi Tom:

Sorry Tom, I am surrounded on four side by a tall brick wall and I cannot
see where you would like me to go. Here are the two queries one of which I
believe you want me to change.

qMstrLst says:

SELECT SalesId, SalesStatus
From MasterList
WHERE (((SalesCD)="S") AND ((SalesStatus)="0" Or (SalesStatus)="1"));

This query will produce a dataset like
Sales15 1
Sales17 0
Sales21 1

I will then open qGetBJInfo and change it to read
SELECT *, 15 as Source
FROM Sales15 IN 'x:\nvtry'[dBASE IV;]
Union all
SELECT *, 17 as Source
FROM Sales17 IN 'x:\nvtry'[dBASE IV;]
UNION ALL
SELECT *, 21 as Source
FROM Sales21 IN 'x:\nvtry'[dBASE IV;];

From here I then run queries, reports, or forms to display data all using
qBJInfo as the source file. Originally I did a make table query from qBJInfo
but after experimenting I found that it was just as fast to run everything
directly from qBJInfo.

Does that help you help me?
Thanks
Bill(with blank stare)
 
Dear Bill:

I've been trying to respond to your need for a code-less solution, at
least within some limits. I was proposing you bring each SalesXX
table's values into a single table, identifying the Source within that
table, and using that, rather than the UNION query which links to the
dBASE IV tables.

You seem to have a workable solution underway. Rather than disturb
that, and interrupt your thinking, let's see how we can finish that
up.

What obstacles do you now face, please?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom:

Sorry Tom, I am surrounded on four side by a tall brick wall and I cannot
see where you would like me to go. Here are the two queries one of which I
believe you want me to change.

qMstrLst says:

SELECT SalesId, SalesStatus
From MasterList
WHERE (((SalesCD)="S") AND ((SalesStatus)="0" Or (SalesStatus)="1"));

This query will produce a dataset like
Sales15 1
Sales17 0
Sales21 1

I will then open qGetBJInfo and change it to read
SELECT *, 15 as Source
FROM Sales15 IN 'x:\nvtry'[dBASE IV;]
Union all
SELECT *, 17 as Source
FROM Sales17 IN 'x:\nvtry'[dBASE IV;]
UNION ALL
SELECT *, 21 as Source
FROM Sales21 IN 'x:\nvtry'[dBASE IV;];

From here I then run queries, reports, or forms to display data all using
qBJInfo as the source file. Originally I did a make table query from qBJInfo
but after experimenting I found that it was just as fast to run everything
directly from qBJInfo.

Does that help you help me?
Thanks
Bill(with blank stare)

Tom Ellison said:
Dear Bill:

See responses inserted below.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




I meant for you to append them as they arrive. Probably you would
also remove them as they become obsolete.


An INNER JOIN between mstrlst and the combined Sales table would give
you only those XX portions desired.
Click to expand...
Click to expand...
 
Hi Tom:

The only real obstacle I really see is the manual intervention I am
currently performing. It is fine in the short term but eventually I need to
find a way to automate this so I am not part of the solution. I am also open
to suggestion for improving my solution especially if it augments my
knowledge of Access. So if coding is the only way to automate my process
then that is what I shall learn. Is it?

At any rate I am a novice to writing code and I do not know if it is
appropriate for me to ask for that kind of help here. (I am sure someone can
let me know if it is not appropriate.) I have cobbled together some macros
tied to forms so the user just clicks for what they want but that is the
extent of my coding skills.

At any rate I would like the second query (or some equivalent procedure) to
somehow use the information from the first query without me having to
change the second query manually. I appreciate you comments

Thanks for all your help
Bill

Tom Ellison said:
Dear Bill:

I've been trying to respond to your need for a code-less solution, at
least within some limits. I was proposing you bring each SalesXX
table's values into a single table, identifying the Source within that
table, and using that, rather than the UNION query which links to the
dBASE IV tables.

You seem to have a workable solution underway. Rather than disturb
that, and interrupt your thinking, let's see how we can finish that
up.

What obstacles do you now face, please?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom:

Sorry Tom, I am surrounded on four side by a tall brick wall and I cannot
see where you would like me to go. Here are the two queries one of which I
believe you want me to change.

qMstrLst says:

SELECT SalesId, SalesStatus
From MasterList
WHERE (((SalesCD)="S") AND ((SalesStatus)="0" Or (SalesStatus)="1"));

This query will produce a dataset like
Sales15 1
Sales17 0
Sales21 1

I will then open qGetBJInfo and change it to read
SELECT *, 15 as Source
FROM Sales15 IN 'x:\nvtry'[dBASE IV;]
Union all
SELECT *, 17 as Source
FROM Sales17 IN 'x:\nvtry'[dBASE IV;]
UNION ALL
SELECT *, 21 as Source
FROM Sales21 IN 'x:\nvtry'[dBASE IV;];

From here I then run queries, reports, or forms to display data all using
qBJInfo as the source file. Originally I did a make table query from qBJInfo
but after experimenting I found that it was just as fast to run everything
directly from qBJInfo.

Does that help you help me?
Thanks
Bill(with blank stare)

Tom Ellison said:
Dear Bill:

See responses inserted below.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Fri, 12 Mar 2004 21:14:11 -0600, "B Nieds"

Hi Tom:

Ok, now you have lost me. Now, I do not know if you lost me because I
Click to expand...
do
not
understand your direction or if it is because I have not been clear enough
in describing the way the inventory files are created.

When you say "create a table to contain all the different SalesXX datasets"
do you mean "Since the SalesXX files already exist then do one massive
append all of the data to one massive collection file and the proceed to
extract and run the necessary report from this massive file?" in which case
I do not see a solution. The SalesXX files do not exist until the inventory
system creates them (based on whatever transaction occurred that day)
Click to expand...
and
that is why the XX keeps changing. So I do not know how to take a SalesXX
datasets (which does not yet exist) and append to a master list.

I meant for you to append them as they arrive. Probably you would
also remove them as they become obsolete.

When I run my query qMstrlst it give me a list of SalesXX files
Click to expand...
created
(and
still active) on which I can run reports. Currently I then use this list
(usually 3-5 file names) and manually change a second query (qBJinfo)
Click to expand...
to
use
these new file names. The qBJinfo query (which uses your UNION and
Click to expand...
that
made
my task much easier) to MakeTable (I never keep any old data) tBJinfo from
which all the reports are generated.

An INNER JOIN between mstrlst and the combined Sales table would give
you only those XX portions desired.


So, what am I not understanding in your answer?

Thanks
Bill

ps: My allergy to coding only stems from the fact that I have zero
experience with coding. I have read the help feature a couple of
Click to expand...
times,
and
have looked at some examples, but I cannot seem to identify the pieces I
need to start. I like learning and when time permits I keep going deeper
into Access but so far coding escapes me.

Dear Bill:

Understanding your allergy to coding, I'll make a proposal. You can
judge whether this would be useful.

Assuming you create a table to contain all the different SalesXX
datasets, with the added Source column, you can write a query that
appends one SalesXX set into this "master" Sales table. If you copy
the desired SalesXX file to a standard file name, and use a parameter
to obtain the XX value from the user, no code would be needed.

Another DELETE query could also use a parameter to specify which
Source set is to be removed. Again, no VBA coding required.

Would such a solution begin to match your needs?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 10 Mar 2004 17:42:02 -0600, "B Nieds"

Hi Tom:

Thanks for the reply. I was afraid it would come down to writing some
code
and this is an area I am totally unfamiliar with. Does what I want
Click to expand...
to
do
require lots of code? I have a query I created to called qmstrlst which
returns the list of file names I will use for todays report. Can I use
this
in the module somehow?

Sorry if these questions are not appropriate but I am not sure were to
start.


Thanks
Bill
Dear Bill:

You could create a module that steps through the rows of mstrlst
Click to expand...
in
a
recordset, creating each SELECT in the UNION query. It would build
the SQL of this query then, dynamically for each day.

If you put all the rows from each SalesXX into a single table, and
perhaps use the value of XX as the Source column, you could then JOIN
to the mstrlst table to find the rows for the current day's work. You
could also purge those that become obsolete.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 9 Mar 2004 20:31:19 -0600, "B Nieds"

Hi Tom:

I wish I were the one creating the SalesXX files because that would
give
me
more control. The SalesXX are files created daily by the inventory
system
and when I look at the reason why it makes sense to do it that
Click to expand...
way
but
it
does make my task more difficult. There is a table (called mstrlst)
which
exists to tell me what file names I need and for which day. I then
manually
change my second query to use these file names and the report gets
created.
Manually is ok for now but I figured that there would have to be some
dynamic way to handle the changing of the file names. My
Click to expand...
question
is
there
some simple way without a lot changes on my part?

I have changed my query to use the UNION approach, it is fun to learn
new
stuff, so thanks for that info.

Thanks
Bill


---- snip -----------
Click to expand...
Click to expand...
Click to expand...
 
Back
Top