Hello people,
I'm fairly new to access and DB programming in general. I am familiar with the "basics" of MS Access, forms, tables, queries, macros, and such, but still don't have everything down pat.
What I am attempting is to create a database which stores client information linked to particular reps detailing dates, financial info, addresses and such. Instead of creating a flat table with all this info inside it, controlled by forms and queries, I wish to divide the data into multiple tables based on project timelines and have one major form act as the data entry front end for all project tables. I also wish to have a separate global table just detailing customer contact info where any customer id can be related to any one or more records in the various project tables.
ie.
table custinfo - storing all customer contact info, nothing else
table project1 - records related to unique identifier in "custinfo" table but only concerning information relative to this project... $$$ totals, dates, etc...
table project2 - likewise, except only dealing with this unique project and the client relations with.
table project 3, and so on... as each project comes into existence, a new table will be created with the same criteria… manually I suppose.
form frontend (whatever the name) - this needs to act as a master form where data can be entered regarding a new client (data to be routed to table "custinfo") and some sort of subform(s) which can link to the various project tables (or a way where a customer name, id can be entered to bring up a display of all client activity if the user so chooses, or a particular project (perhaps determined by a dynamic query?)
I also need to create reports on all the above, both totals within project tables as well as across project tables.
Any suggestions on table structure, or relationships would be greatly appreciated. I've already created the tables, but am stuck on the relationships and/or linking tables.
MS Access 2003//Windows XP latest patches
Thank you for any help you can provide!!! ;-)
- BSM ->
I'm fairly new to access and DB programming in general. I am familiar with the "basics" of MS Access, forms, tables, queries, macros, and such, but still don't have everything down pat.
What I am attempting is to create a database which stores client information linked to particular reps detailing dates, financial info, addresses and such. Instead of creating a flat table with all this info inside it, controlled by forms and queries, I wish to divide the data into multiple tables based on project timelines and have one major form act as the data entry front end for all project tables. I also wish to have a separate global table just detailing customer contact info where any customer id can be related to any one or more records in the various project tables.
ie.
table custinfo - storing all customer contact info, nothing else
table project1 - records related to unique identifier in "custinfo" table but only concerning information relative to this project... $$$ totals, dates, etc...
table project2 - likewise, except only dealing with this unique project and the client relations with.
table project 3, and so on... as each project comes into existence, a new table will be created with the same criteria… manually I suppose.
form frontend (whatever the name) - this needs to act as a master form where data can be entered regarding a new client (data to be routed to table "custinfo") and some sort of subform(s) which can link to the various project tables (or a way where a customer name, id can be entered to bring up a display of all client activity if the user so chooses, or a particular project (perhaps determined by a dynamic query?)
I also need to create reports on all the above, both totals within project tables as well as across project tables.
Any suggestions on table structure, or relationships would be greatly appreciated. I've already created the tables, but am stuck on the relationships and/or linking tables.
MS Access 2003//Windows XP latest patches
Thank you for any help you can provide!!! ;-)
- BSM ->
Comment