B
briana.askew
Here's what I have so far:
Every month I pull a bunch of reports from a website. I work for the Army
and it is a list of names of people in different Army branchs. Each branch
has multiple units so I have to pull the list by unit and put them in Excel
spreadsheets so they can be combined for each branch. This leaves me with
about 50 Excel spreedsheets in one workbook each branch made up of about 5
units. I've created a Access Database that links to each Excel spreedsheet in
this workbook and which creates a table for each spreedsheet (I know the
design is strange and not ideal but if you saw the way I have to get this
info you would understand there is no other way). I need the database to
query whatever table I want it to and then give me a report based on that
query (the criteria for the query never changes). I started by creating
queries and reports for every table and that left me with a huge database
that was slow and crashed all the time. I also realized that if I ever leave
this job and the Army comes out with a new branch this program would be
useless cause no one else in my office would know how to create the queries
and the reports to pull that branch. I figured creating a table for a new
branch would be easy and I could teach that to someone here but everything
else would have to be automatically updated. I asked a question here the
other day about how to change the table name in a query based on the users
choice in a form, I got some code that didn't really work (I'm probably doing
something wrong!)but it definitly put me on the right track. I've created one
form that asks the user what branch they want to pull a report on using a
combo box. The recordsource for the combo box is a query on the msysobjects
table so (if understand correctly) all my coworkers would have to do is
create a new table for the new branch and link it to it's Excel spreadsheet
and that would automatically update in the msysobjects table making it an
vailable choice in my combo box. The form then uses the choice made to open a
report. This is workign fine and the report does recognize the table name
from the user's choice; however, it is giving me a report of all the table's
records and only need those that meet the criteria I want. How can I get the
report to look at one field (the field is called "missing") and only display
those records that havea value in them or have a particular value in them? I
can't do a query because I can't figure out how to get the query to change
it's tab;e source based on a user entry in a form... Any suggestions?!!?
Thanks
Every month I pull a bunch of reports from a website. I work for the Army
and it is a list of names of people in different Army branchs. Each branch
has multiple units so I have to pull the list by unit and put them in Excel
spreadsheets so they can be combined for each branch. This leaves me with
about 50 Excel spreedsheets in one workbook each branch made up of about 5
units. I've created a Access Database that links to each Excel spreedsheet in
this workbook and which creates a table for each spreedsheet (I know the
design is strange and not ideal but if you saw the way I have to get this
info you would understand there is no other way). I need the database to
query whatever table I want it to and then give me a report based on that
query (the criteria for the query never changes). I started by creating
queries and reports for every table and that left me with a huge database
that was slow and crashed all the time. I also realized that if I ever leave
this job and the Army comes out with a new branch this program would be
useless cause no one else in my office would know how to create the queries
and the reports to pull that branch. I figured creating a table for a new
branch would be easy and I could teach that to someone here but everything
else would have to be automatically updated. I asked a question here the
other day about how to change the table name in a query based on the users
choice in a form, I got some code that didn't really work (I'm probably doing
something wrong!)but it definitly put me on the right track. I've created one
form that asks the user what branch they want to pull a report on using a
combo box. The recordsource for the combo box is a query on the msysobjects
table so (if understand correctly) all my coworkers would have to do is
create a new table for the new branch and link it to it's Excel spreadsheet
and that would automatically update in the msysobjects table making it an
vailable choice in my combo box. The form then uses the choice made to open a
report. This is workign fine and the report does recognize the table name
from the user's choice; however, it is giving me a report of all the table's
records and only need those that meet the criteria I want. How can I get the
report to look at one field (the field is called "missing") and only display
those records that havea value in them or have a particular value in them? I
can't do a query because I can't figure out how to get the query to change
it's tab;e source based on a user entry in a form... Any suggestions?!!?
Thanks