Generic tables

[Guest]

Within an Access 2003 database I have a number of reports with associated
queries,forms, etc. They use a set of tables such as Customer1, Orders1.

If I need to use to use a different set of tables (but the same table
layout), must I duplicate everything or is their a 'generic' way of achieving
this?

Any help greatly appreciated.

 

[Brendan Reynolds]

If you are using these tables to divide your data into subsets of records,
the solution is to not do that - keep the data in one table and use queries
to select subsets of records from that table.

A good general rule is that whenever you find yourself using names that end
with numbers, you should stop and think about what you're doing. There are,
of course, exceptions to every rule, but more often than not, this is a
symptom of a flawed database design.

If your data is in one table, you can then use one report based on one
parameter query to select many different subsets of records. For example, to
print a list of customers in a specific city, you would base the report on a
query such as ...

SELECT * FROM Customers WHERE City = [City?]

 

[Guest]

Thank you for your reply Brendan.

I used those suffixes for illustration only. In practice the data is created
by another (non-access) system and is stored in tables within folders for a
specific year, Data2004, Data2005 etc.

My reporting-system database has imported the tables for 2004. But I need to
import (or link) several years so that users can choose the year to report
on. But I didn't want to have a saparate copy of each report/query etc for
each year.

If you are using these tables to divide your data into subsets of records,
the solution is to not do that - keep the data in one table and use queries
to select subsets of records from that table.

A good general rule is that whenever you find yourself using names that end
with numbers, you should stop and think about what you're doing. There are,
of course, exceptions to every rule, but more often than not, this is a
symptom of a flawed database design.

If your data is in one table, you can then use one report based on one
parameter query to select many different subsets of records. For example, to
print a list of customers in a specific city, you would base the report on a
query such as ...

SELECT * FROM Customers WHERE City = [City?]

--
Brendan Reynolds (MVP)

Within an Access 2003 database I have a number of reports with associated
queries,forms, etc. They use a set of tables such as Customer1, Orders1.

If I need to use to use a different set of tables (but the same table
layout), must I duplicate everything or is their a 'generic' way of
achieving
this?

Any help greatly appreciated.

 

[Brendan Reynolds]

But why not import the data into one table in your Access database?

That said, though, if the table names follow a predictable pattern, such as
"Data2004", "Data2005", as in your example, you could do something like this
....

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "Data" & InputBox("Year?")

End Sub

--
Brendan Reynolds (MVP)

Thank you for your reply Brendan.

I used those suffixes for illustration only. In practice the data is
created
by another (non-access) system and is stored in tables within folders for
a
specific year, Data2004, Data2005 etc.

My reporting-system database has imported the tables for 2004. But I need
to
import (or link) several years so that users can choose the year to report
on. But I didn't want to have a saparate copy of each report/query etc for
each year.

If you are using these tables to divide your data into subsets of
records,
the solution is to not do that - keep the data in one table and use
queries
to select subsets of records from that table.

A good general rule is that whenever you find yourself using names that
end
with numbers, you should stop and think about what you're doing. There
are,
of course, exceptions to every rule, but more often than not, this is a
symptom of a flawed database design.

If your data is in one table, you can then use one report based on one
parameter query to select many different subsets of records. For example,
to
print a list of customers in a specific city, you would base the report
on a
query such as ...

SELECT * FROM Customers WHERE City = [City?]

--
Brendan Reynolds (MVP)

Within an Access 2003 database I have a number of reports with
associated
queries,forms, etc. They use a set of tables such as Customer1,
Orders1.

If I need to use to use a different set of tables (but the same table
layout), must I duplicate everything or is their a 'generic' way of
achieving
this?

Any help greatly appreciated.

 

[Guest]

Thanks once again Brendan.

I think my first post mislead you. I am not trying to split my data.
The data for each year is already held in its own folder, e.g.
DATA2004,DATA2005 and each contains:
Courses.dbf, Students.dbf, Log.dbf, Codes.dbf & many others. I have imported
the tables for 2004 into my report database.

I have several reports, currently running with this 2004 data, and each with
its own query as the datasource. Each query will use a different combination
of the tables but most will use these 4.

I want to import the tables for other years also. So an import from the
DATA2005 directory will initially give me:
Courses1.dbf, Students1.dbf etc., Access automatically adding the suffixes.


So how do I get my set of queries/reports to look at either
courses.dbf/Students.dbf etc or Courses1.dbf/Students1.dbf etc depending on
the year selected? Or must I have a separate set of queries/reports for each
year?

But why not import the data into one table in your Access database?

That said, though, if the table names follow a predictable pattern, such as
"Data2004", "Data2005", as in your example, you could do something like this
....

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "Data" & InputBox("Year?")

End Sub

--
Brendan Reynolds (MVP)

Thank you for your reply Brendan.

I used those suffixes for illustration only. In practice the data is
created
by another (non-access) system and is stored in tables within folders for
a
specific year, Data2004, Data2005 etc.

My reporting-system database has imported the tables for 2004. But I need
to
import (or link) several years so that users can choose the year to report
on. But I didn't want to have a saparate copy of each report/query etc for
each year.

If you are using these tables to divide your data into subsets of
records,
the solution is to not do that - keep the data in one table and use
queries
to select subsets of records from that table.

A good general rule is that whenever you find yourself using names that
end
with numbers, you should stop and think about what you're doing. There
are,
of course, exceptions to every rule, but more often than not, this is a
symptom of a flawed database design.

If your data is in one table, you can then use one report based on one
parameter query to select many different subsets of records. For example,
to
print a list of customers in a specific city, you would base the report
on a
query such as ...

SELECT * FROM Customers WHERE City = [City?]

--
Brendan Reynolds (MVP)

Within an Access 2003 database I have a number of reports with
associated
queries,forms, etc. They use a set of tables such as Customer1,
Orders1.

If I need to use to use a different set of tables (but the same table
layout), must I duplicate everything or is their a 'generic' way of
achieving
this?

Any help greatly appreciated.

 

[Brendan Reynolds]

As I've said, the best solution is to put the separate tables into one table
in the Access database, then you can use one report based on a parameter
query instead of separate queries and reports. Just because the third-party
application uses a separate table for each year does not force you to do the
same thing in Access. You could write code to automate the import, but if
you only import once a year or so, you can simply create an append query to
append the data from Courses1 to Courses.

--
Brendan Reynolds (MVP)

Thanks once again Brendan.

I think my first post mislead you. I am not trying to split my data.
The data for each year is already held in its own folder, e.g.
DATA2004,DATA2005 and each contains:
Courses.dbf, Students.dbf, Log.dbf, Codes.dbf & many others. I have
imported
the tables for 2004 into my report database.

I have several reports, currently running with this 2004 data, and each
with
its own query as the datasource. Each query will use a different
combination
of the tables but most will use these 4.

I want to import the tables for other years also. So an import from the
DATA2005 directory will initially give me:
Courses1.dbf, Students1.dbf etc., Access automatically adding the
suffixes.


So how do I get my set of queries/reports to look at either
courses.dbf/Students.dbf etc or Courses1.dbf/Students1.dbf etc depending
on
the year selected? Or must I have a separate set of queries/reports for
each
year?

But why not import the data into one table in your Access database?

That said, though, if the table names follow a predictable pattern, such
as
"Data2004", "Data2005", as in your example, you could do something like
this
....

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "Data" & InputBox("Year?")

End Sub

--
Brendan Reynolds (MVP)

Thank you for your reply Brendan.

I used those suffixes for illustration only. In practice the data is
created
by another (non-access) system and is stored in tables within folders
for
a
specific year, Data2004, Data2005 etc.

My reporting-system database has imported the tables for 2004. But I
need
to
import (or link) several years so that users can choose the year to
report
on. But I didn't want to have a saparate copy of each report/query etc
for
each year.


:

If you are using these tables to divide your data into subsets of
records,
the solution is to not do that - keep the data in one table and use
queries
to select subsets of records from that table.

A good general rule is that whenever you find yourself using names
that
end
with numbers, you should stop and think about what you're doing. There
are,
of course, exceptions to every rule, but more often than not, this is
a
symptom of a flawed database design.

If your data is in one table, you can then use one report based on one
parameter query to select many different subsets of records. For
example,
to
print a list of customers in a specific city, you would base the
report
on a
query such as ...

SELECT * FROM Customers WHERE City = [City?]

--
Brendan Reynolds (MVP)

Within an Access 2003 database I have a number of reports with
associated
queries,forms, etc. They use a set of tables such as Customer1,
Orders1.

If I need to use to use a different set of tables (but the same
table
layout), must I duplicate everything or is their a 'generic' way of
achieving
this?

Any help greatly appreciated.