First Database Is Not Going So Well

[hbs480]

I work for a construction company that hired me to put all of their purchasing data into Excel so they could build budgets and price houses accordingly. It took a few months but we finally got everything loaded and things were great for a while.

To get started I will list this out the best I can:

I have 40 plans .
I have 3 levels that each plan can be built in.
I have about 70 items that go into a budget for various materials and labor costs.


I have about 1-5 vendors with different prices for each item (laborers or material providers) that I can use.

Now Excel is ok to get a preliminary budget, but the owners of the company now want me to show them different budgets depending on if we switch vendors, if we build the house in a different level etc. Excel is unfortunatley not good with change, and requires a lot of manual work to update each budget. Plus we are constantly adding new vendors and even plans making keeping up with budgets next to impossible. In Excel I have to change cells in the budget constantly by hand to reflect new prices

My thought was that Access could make my life a lot easier and automate some of this process.

However, Excel and Access seem to be completely different animals and I am having problems getting the simplest things to work.

For instance:

I manually typed the budget sheet into Access for each plan.
Then I manually typed in a sheet showing the cost for kitchen appliances into another sheet. Now here is where it gets tricky - I have 5 appliance packages to choose from (standard electric, standard gas, gourmet electric, gourmet gas, Premium Gourmet). I set each one up these up as separate columns in my appliance sheet.
I set up a relation between my budget sheet and my appliance sheet
I thought that this would give me a drop down to select which package I wanted for each budget, but instead it lists the entire table.


A lot of my budget sheets work like this (some are a little more complex but I will get to those later). Does anyone know what I am doing wrong?

[CJ_London]

Unfortunately Access is not like excel, but definitely more appropriate to what you want to do.

With excel, data tends to be stored 'short and wide' - per your example. And where and how you store it forms part of the 'presentation'

With Access (or any db system) the data is stored 'tall and narrow'. There is also the concept of normalisation which is also applied (in simple terms, data is stored only once and fields should not be used in defining the data) - so the data you have would actually be stored in a number of tables. And you use forms and reports which use queries to display the data the way you want

In your case the columns with prices are being used to define your data.

Would need to know a lot more about your business model but from the data provided I suggest your tables would be something like

tblAppliances
AppliancePK autonumber
ApplianceDesc text

tblModels
ModelPK autonumber
ApplianceFK long (links back to AppliancePK)
ApplianceDesc text (e.g. standard elec, standard gas, etc)

tblPrices
PricePK autonumber
ModelFK long
Price currency
PriceFrom Date

[hbs480]

So if I am understanding you correctly ( and I may not be because this is a new concept to me). I need to convert most of my horizontal sheets so that they are verticle, and break out my excel tables into multiple access tables ( I.E. standard electric, standard gas etc all need to be their own table). Is this correct?

I guess I should have attached this, but this is an example excel budget sheet that is the end result I need for the database. Right now I probably have it set up wrong in Access because I made it horizontal.

[ItsMe]

...I need to convert most of my horizontal sheets so that they are verticle, and break out my excel tables into multiple access tables...

In a nutshell, yes. However, this is not an easy task. The learning curve will be steep. Also, operations that depend on Excel are difficult to transition to a relational database. I would recommend getting some help. If you can find someone local to you, that may be best. This way, they can get intimate with Operations and the Business Rules quickly.

[CJ_London]

and break out my excel tables into multiple access tables ( I.E. standard electric, standard gas etc all need to be their own table). Is this correct?

Not quite, and the correct answer depends on your business rules. From the data now supplied I would suggest something like

tblAppliances
AppliancePK..ApplianceDesc
1.................Standard Electric
2.................Standard Gas
3.................Gourmet Electric
etc

tblPrices
PricePK..ApplianceFK..Price.......... PriceFrom
1..........1..................$1,761.95...01/01/2014
2..........2..................$1,895.83...01/01/2014
3..........3..................$3,784.26...01/01/2014
4..........4..................$4,105.00...01/01/2014
5..........1..................$1,861.95...01/01/2015
6..........2..................$1,995.83...01/01/2015
7..........3..................$3,984.26...01/01/2015
8..........4..................$4,305.00...01/01/2015
etc
the reason for keeping prices in a separate table is because prices can change - but the appliance description doesn't - and you can use the date for the system to determine which price to apply

then in your other tables

tblPlans
PlanPK...PlanDesc....ApplianceFK
1...........Ash...........1
2...........Beech........1
3...........Birch.........1
4...........Bradford....1
etc

then this query will select the price designated for an appliance specified in a plan

SELECT PlanDesc, ApplianceDesc, Price
FROM (tbPlans INNER JOIN tblAppliances ON tblPlans.ApplianceFK= tblAppliances.AppliancePK) INNER JOIN tblPrices ON tblAppliances.AppliancePK=tblPrices.ApplianceFK
WHERE PriceFrom>=#01/01/2015#

and this will provide all appliance prices for all plans

SELECT PlanDesc, ApplianceDesc, Price
FROM tbPlans, tblAppliances INNER JOIN tblPrices ON tblAppliances.AppliancePK=tblPrices.ApplianceFK
WHERE PriceFrom>=#01/01/2015#

and this will provide a similar view of your data as you currently have in your Appliance table

TRANSFORM First(Price) AS currentPrice
SELECT PlanDesc
FROM tbPlans, tblAppliances INNER JOIN tblPrices ON tblAppliances.AppliancePK=tblPrices.ApplianceFK
WHERE PriceFrom>=#01/01/2015#
GROUP BY PlanDesc
PIVOT ApplianceDesc