Multiple Table lookup

[Maria315]

I have an database with weekly tables for contributions. I would like
to build a form that will allow users to select the table they want
from a drop down or parameter. I can work with Macros but not so good
with SQL. Is it possible to create a parameter query and link to the
form or a macro which would call for the table?

 

[Ryan]

Make another table and put in it the names of your weeks. Then create a form
for each of your weak tables and use the same name you just put in your weeks
table. Next create a form for them to choose the week, add a dropdown to the
form, name it WeekFilter, and set its conotrol source to your new weeks
table. Add a comand button to your form and call it OpenWeek. In the on
click event of the command button add this code.

Private Sub OpenWeek_Click()
Dim WeekForm As String
WeekForm = Me!WeekFilter

DoCmd.OpenForm WeekForm
End Sub

This will take the value you selected in your dropdown and open the form
with that name.

Hope this helps.

 

[Steve]

You have encountered the problem that occurs when tables are not
normaslized. If possible you need to change the design of your tables to
something like:

TblContributionWeek
ContributionWeekID
ContributionWeek
<Other contribution week fields you need>

TblContribution
ContributionID
ContributionWeekID
Contribution
<Other contribution fields you need>

Changing the table structure is not as difficult as it may appear. You need
to use append queries and or update queries to move existing data into the
new tables. Then you need to modify any queries, forms and reports that use
your existing weekly tables.

Steve

 

[Klatuu]

You first mistake is you are using a different table for each week. This
will actually make things much harder for you. The correct method would be
to have one table for all contributions with a field that identifies the week
and year of the contributions. In fact, if you already have a contribution
date in the table, that field will suffice.

Then when you need to see contributions for a specific week, you use a query
that is filtered on that week.