control source to switch tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table for each reporting year for the same clients. I need to be
able to switch from one year to another in a form. I have a combo box for
moving to the desired client, but I'm stuck on how to set up the Year field
on the form to move from one table to another. Example:

table2000
client_# prod_amt
312 436
313 142

table2001
client_# prod_amt
312 379
313 655

If I have a form that has an entry field for the reporting year, the client
#, and the product amount with a combo box for looking up the client #, how
can I get to the appropriate year's table?

Thanks!
Sandra
 
I have a table for each reporting year for the same clients. I need to be
able to switch from one year to another in a form. I have a combo box for
moving to the desired client, but I'm stuck on how to set up the Year field
on the form to move from one table to another. Example:

table2000
client_# prod_amt
312 436
313 142

table2001
client_# prod_amt
312 379
313 655

If I have a form that has an entry field for the reporting year, the client
#, and the product amount with a combo box for looking up the client #, how
can I get to the appropriate year's table?

Thanks!
Sandra
Click to expand...

Sandra,
I think you have a poorly designed database.
A table for each year for each client? That's not the way to use
Access.

A proper design would be one master table and at least a table for
customers, with perhaps sub-tables for whatever additional data you
are entering.
Then filter the table according to the year and/or customer for
reporting purposes.
 
No, I have a table for each year with all of the clients and data in each
table. In other words, for 2000 I have a table that lists all clients and
all data for that year, for 2001 I have a duplicate table that has the new
info for that year's data. I did not set up the tables, but I'm stuck trying
to get all of this data into a better format and set up a new database that
works better. There is a huge amount of data, so there are many columns in
the tables. The only thing that changes between the tables is the data
values entered.
 
In fact, just from a pure table design view, it would be better if all your
client data were in the same table, identified by year or date. That way,
you could use criteria (WHERE clause) to select both the year and the
client. With the design you have, you are "storing data in the table name"
(year) which is making your selection more complicated than it need be.

Even so, if you use separate Combo Boxes on the selection Form, one for Year
(to determine the Query to use, referring to a specific Table for a Year)
and the other for Client identification. You could construct the entire SQL
statement in code, or store a basic Query for each Year, and retrieve and
modify its SQL property -- you could store the modified SQL statement with
WHERE clause back into the Query's SQL property, or you could pass the
modified SQL statement in the OpenArgs argument of the DoCmd.OpenReport and
use it to set the Report's RecordSource in the Open Event of the Report.

Larry Linson
Microsoft Access MVP
 
That makes sense. I'm not proficient in writing SQL statements, so putting
the data into one table seems the best route. How do I go about setting up
the combo box so that I can retrieve the record for a specific client for a
specific year assuming that I do have everything in one table? Let's say my
table is called tblWaterData and I want to be able to retrieve the data for
client XYZ in the year 2000. How would I set up a combo box or boxes on a
form to cause the info shown below to appear in the fields on the form? I
want the user to be able to type in the reporting year and the ID# to
retrieve the record. I also need to be able to add new years to the database.

tblWaterData

Reporting_Yr ID# Client_Name Data_Field1 Data_Field2
2000 312 ABC 43 56
2000 313 XYZ 65 23
2001 312 ABC 99 53
2001 313 XYZ 31 86

Thanks,
Sandra
 
That makes sense. I'm not proficient in writing SQL statements, so putting
the data into one table seems the best route. How do I go about setting up
the combo box so that I can retrieve the record for a specific client for a
specific year assuming that I do have everything in one table? Let's say my
table is called tblWaterData and I want to be able to retrieve the data for
client XYZ in the year 2000. How would I set up a combo box or boxes on a
form to cause the info shown below to appear in the fields on the form? I
want the user to be able to type in the reporting year and the ID# to
retrieve the record. I also need to be able to add new years to the database.

tblWaterData

Reporting_Yr ID# Client_Name Data_Field1 Data_Field2
2000 312 ABC 43 56
2000 313 XYZ 65 23
2001 312 ABC 99 53
2001 313 XYZ 31 86

Thanks,
Sandra
Click to expand...

As criteria for the query:
Where tblWaterData.[ID#] = forms!FormName!CombolName and
tblWaterData.Reporting_Yr = forms!FormName!ControlName

The above assumes that Reporting_Yr contains just the year (2005).
If in fact it contains a valid date, (7/15/2005) then use:
..... and Year([Reporting_Yr]) = forms!FormName!ControlName

Change FormName and the Control names to whatever your actual form and
control names are.

In either case, find the Client in the combo box, then enter the year
wanted in the other control.
The form must be open when the query/report is run.

Note regarding your use of ID#. The # symbol, in Access, is a
date/time delimiter symbol. It's not a good idea to use the # symbol
in a field name. At some point Access will throw an error as it gets
confused. Best to change it to something else (ID or ClientID, etc.).
At the very least, if you cannot for some reason change it, always
enclose that field name within brackets [ID#] (as I did above.).
 
Thanks, I'll give it a try. In my table I actually have ID_No instead of
ID#, so it shouldn't be a problem.

fredg said:
That makes sense. I'm not proficient in writing SQL statements, so putting
the data into one table seems the best route. How do I go about setting up
the combo box so that I can retrieve the record for a specific client for a
specific year assuming that I do have everything in one table? Let's say my
table is called tblWaterData and I want to be able to retrieve the data for
client XYZ in the year 2000. How would I set up a combo box or boxes on a
form to cause the info shown below to appear in the fields on the form? I
want the user to be able to type in the reporting year and the ID# to
retrieve the record. I also need to be able to add new years to the database.

tblWaterData

Reporting_Yr ID# Client_Name Data_Field1 Data_Field2
2000 312 ABC 43 56
2000 313 XYZ 65 23
2001 312 ABC 99 53
2001 313 XYZ 31 86

Thanks,
Sandra
Click to expand...

As criteria for the query:
Where tblWaterData.[ID#] = forms!FormName!CombolName and
tblWaterData.Reporting_Yr = forms!FormName!ControlName

The above assumes that Reporting_Yr contains just the year (2005).
If in fact it contains a valid date, (7/15/2005) then use:
..... and Year([Reporting_Yr]) = forms!FormName!ControlName

Change FormName and the Control names to whatever your actual form and
control names are.

In either case, find the Client in the combo box, then enter the year
wanted in the other control.
The form must be open when the query/report is run.

Note regarding your use of ID#. The # symbol, in Access, is a
date/time delimiter symbol. It's not a good idea to use the # symbol
in a field name. At some point Access will throw an error as it gets
confused. Best to change it to something else (ID or ClientID, etc.).
At the very least, if you cannot for some reason change it, always
enclose that field name within brackets [ID#] (as I did above.).
Click to expand...
 
Back
Top