If someone could point me in the right direction and let me know if what I am trying to do is even possible I would greatly appreciate it.
I am working with multiple customers of ours point of sale data, currently I have 3 customers of ours sales data setup in tables, one table for each customer per month, from 2006 to present month. For example a table name would be Customer1-200807 for their sales data for July of 2008. There would also be a Customer2-200807, etc. I have about 25 different fields in each of the tables and I am using the customers product number for the primary key in each table.
I pull this data every month manually from our customers and then import into the database as a table.
I am fairly confident my database structure is good, as I have been running queries every month rolling up quarterly and annual sales without a problem.
What I would like to be able to do is give some other users the ability to simply run some queries based on some date range criteria they input into a form. I have a form setup with a 3 pull downs one for the customer, one for the month and the year they want to run the query for.
I guess what I am trying to do given my database structure is have a query that is ran from the form that uses ONLY the tables required for that specific month. Given my naming structure for the tables I think this would be possible.
My field names in each of the customers monthly tables are identical in respective to each customer. One customer has variation in the fields and field names from another...
Any input would be much appreciated, if there is additional info I can provide to help explain please let me know.
Thanks!!
I am working with multiple customers of ours point of sale data, currently I have 3 customers of ours sales data setup in tables, one table for each customer per month, from 2006 to present month. For example a table name would be Customer1-200807 for their sales data for July of 2008. There would also be a Customer2-200807, etc. I have about 25 different fields in each of the tables and I am using the customers product number for the primary key in each table.
I pull this data every month manually from our customers and then import into the database as a table.
I am fairly confident my database structure is good, as I have been running queries every month rolling up quarterly and annual sales without a problem.
What I would like to be able to do is give some other users the ability to simply run some queries based on some date range criteria they input into a form. I have a form setup with a 3 pull downs one for the customer, one for the month and the year they want to run the query for.
I guess what I am trying to do given my database structure is have a query that is ran from the form that uses ONLY the tables required for that specific month. Given my naming structure for the tables I think this would be possible.
My field names in each of the customers monthly tables are identical in respective to each customer. One customer has variation in the fields and field names from another...
Any input would be much appreciated, if there is additional info I can provide to help explain please let me know.
Thanks!!
