Multiple databases to one master database for pricing program

[sliderslider]

Well as the title suggests, to begin i'm farily new to databasing and coding. I'm an engineer with limited experience in building programs, i'm using this as a learning tool so i can develop small single user programs. I'm starting out with a pricing program for the sales reps. Basically the program is very simple, you choose in the forms the selection of the equipement that will be used on site. I could explain more but it doesn't outline the problems i'm looking to solve.

First of all i have multiple databases

a form for drop down comboboxes, and as well as pricing for the equipment,time, hours worked etc....

Basically in the end once the program has been run through i want all that data that has been chosen to be on a master file which i then can generate a set of equations which will spit out a number.

I believe from reading around i have to make relationships between the databases.

The masterfile i want everything to be saved to is also the file i created with client information (master_survey.accdb), i use this information to populate each form with the desired information needed on that form. But as some of the selections are based on dropdown comboboxes that i 'bound' to the say cbo_form.accdb (this was only created so i can changed the options of the comboboxes fairly simple). What i did was opened up master_survey.accdb and linked all the databases to that file, so i can open up that file and i see all the tables. I believe i will have to make 1-1 relationships between the tables i want to so that all the data will be placed where i put the 1-1. So on the final step of the program i would have all the desired information that makes up the final pricing in one database which i could the set up a few equations that will spit out the final pricing.

That masterfile will create a new file with the same extension (master_survey.accdb) but in a new folder. Basically it will keep a new record each time the program adds a new client. This can be chosen from a datagrid at the begining of the program so you can look back at the 'job' or simple modifiy it again.

I hope someone out there can understand what im trying to explain and point me in the right direction.

Regards

[June7]

By 'multiple databases' do you really mean tables?

[sliderslider]

nope i have 6 database files (.accdb) they all have 6+ tables inside.

But now that i have linked the database files to 1 i can view all the tables in the 1 main database.

[June7]

Yes, and that begs the question - Why multiple databases?

[sliderslider]

Well this is the direction i was put by a software engineer.
The only reason i can think of as to why multiple databases is because the main survey file is accessable to anyone in the office(and has to be) this file i'll generate through crystal reports as well.
But why he sent me in that direction i believe is because of the pricing files i have made are password protected.
Specifiacally the work i put into generating the pricing scheme i'd rather keep as my own work and not have it publicly avaliable.

In the grande scheme of things i really only have 3 main databases that are the big hunk of my program.
1.master_survey.accdb - Tables that populate each form with desired information
2.Pricing.accdb pricing + Saves user information
3.Filtertable.accdb (datagrid) this is a fairly simple database which could be added to the master_survey.accdb but i never created it.

All the other databases i have created were made seperate because i dont want them to be saved with (master_survey.accdb). For instance thats why i created a cbo_forms.accdb just to populate all the comboboxes seperate.

So how it looks from my end is i have master_survey.accdb this file contains all the form populating information, this is the file that will be saved and avaliable to be viewed. It will also be created each time a user adds a new job and all that information will be copied and saved. #2 (pricing.accdb) will be for most of the pricing of the equipment (this can vary as 2 variables come to play, what kind of equipment and the hours used) this pricing file will also save all users and the clients personal information. (this will be a passworded file). This program will generate the pricing due to equipment age/monthly hours of operation/travel costs/to quantity. Inside i have markup multipliers/tax multipliers/locale multis/burdan rate/hourly wage costs/equipment factors/ and agreement multi(used cause there're 6 prices generated for the customer to chose from).

I hope this is making a little sense to you. I have been working on this with only help found from the internet.

[June7]

Are you proposing relating the linked tables to each other - on what common unique ID. If each linked table is a set of pricing info, seems to me you might want a UNION so all pricing info will be available as a single set of fields, not multiple similar fields (non-normalized structure).

Then how is pricing record associated with master_survey table? If each linked table uses autonumber field as primary key then the primary key alone is not unique ID for pricing record for saving as foreign key.

[sliderslider]

Well pardon me as its a steep learning curve.

Each table has its own UniqueID, the UniqueID will be created each time a user creates a new proposal.

From thinking about this over night i realized that the pricing tables and master_survey dont correlate as i never created UniqueIDs from the pricing scheme.
To make matters a little trickier the pricing tables are developed on lets say Motor size but it also correlates to how many hours this motor will run a month.

I think i'll have to create a seperate table/database so i can organize quantity, pricing of equipment and allow all my factors and multipliers.
The program will price out 6 prices (as suggested above) they all have different variables that determines the overall pricing.

I do need to have the master_survey to have only clinet information as well as quantity,hours of operation....and so on as office review and also reloading that selected proposal to modify later down the road.

Now what i cant really wrap my head around is having the master_survey with all the information but once i press calculate it runs and uses both the pricing and master_survey to calculate the total prices.

So what i think its going to look like, i have the master_survey user adds information adding equipment ect...Specific fields will then be transfered (i don't know if this is even possible) to a new calculating database that will have all the factors and mulitpliers for each of the 6 catagories of overall pricing. A query would be run to allow simple calculations for instance [Quantity]*[PriceEach]*[taxmult]*[markupmultis]=[answer to be displayed] This is an example of calculations but i hope you understand.

Reaserching the UNION is exactly what i think i'll have to do.