New User. Form help

[Bschmale]

I am new to using access am having trouble with my forms. I want to have a cascading form that changes the fields below it. Much like the following video: http://www.datapigtechnologies.com/f...tomfilter.html



I think my problem lies in the design of my tables. I currently have two tables. One is company financials and the second is company operations, both of the tables have many fields. I want to be able to select one of two areas from a drop down menu and then have the following fields change. I have the following fields set up as A1LOE, A2LOE, A1ProductionExpense, A2ProductionExpense and so on.

I guess what I am getting at is whether or not I have set up my fields and tables set up correctly. Any help is appreciated please let me know if I need to include more information.

[June7]

Sounds like non-normalized data structure to me.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Bschmale]

Non-normalized data is probably right. Again, I am a very new user so any help you would be able to provide me would be greatly appreciated.

[June7]

There are no comboboxes on the form. I don't see any attempt to implement the filter procedure.

Should not use spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be Area2PctOfTotal.

Saving calculated data, especially aggregate calcs, is usually a bad idea and requires code.

Multiple similar name fields indicates non-normalized structure. This can cause you lots of headache in data analysis. It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts. I have a database that violates normalization but it suits our needs and we do very little analysis of the data, just report it.

If you haven't completed a tutorial book, suggest you at least go thru this http://www.rogersaccesslibrary.com/

[Bschmale]

Thank you for your response. I deleted the combo box attempt because I didn't get far enough to be helpful.

Thanks for the tip. I will go though and remove punctuation and the remaining spaces.

Are you saying that I should not use data that is calculated outside of accses? Most of this data is manually entered into an excel spreadsheet so that is where the calculations have been made. Most of the data that has been calculated is important to the db.


I have completed some tutorials but I guess I'm still not understanding normalization. It makes sense when I look at examples but when it comes to normalizing my data I can't seem to understand what that would actually look like.

[June7]

I am saying do not (at least not without strong justification) save results of calculations done in Access. For example, a table has fields Quantity, Amount. Quantity * Amount would be a calc to determine line Total. Would not save this calculated result. You have fields that made me think they would hold data from calcs in Access (the fields with % in name).

Example of your non-normalized structure. 3 fields: FY13, FY14, FY15. Will have to add a new field each year. Adding a field means modifying table, queries, forms, reports, code.

A normalized structure would be another table:

FY	Metrics	CompFinID
2013	abc	1
2014	def	1
2015	ghi	1
2016	jkl	1
2013	mno	2
2014	pqr	2
2015	stu	2
2016	vwx	2

Adding another year is simply adding a record. Does not require modifying any design.

Same for CapXFY12, CapXFY13, CapXFY14, CapXFY15.

Access has a limit of 255 fields in a table. Could eventually run out.

[Bschmale]

So at the point I am at now would it be better to start over or too edit what I have? Would I make more than one row for each company in order to have things like Fiscal Year in one column?

If I were to make more than one row for each column would it then produce duplicate records on my forms?

Again thank you for answering my very novice questions!

Edit:
Looking at your example, are you saying that I should make a new table for the fields that need normalization and than link it via the company Id field?

[June7]

More than one row for each company - as shown in example table.

The rows are not duplicates because not all the data is identical so don't know what you mean by 'duplicate records on my forms'.

The link would be on Company Financials ID.

Please note that the ID fields are autonumber in your current tables. This means that the CompanyID in CompanyOperations is not guaranteed to be for the same company as in CompanyFinancials - at least should not depend on it.

I feel that you are a long way from understanding relational database principles and how to apply them.

Start db design by identifying data entities and how they relate as well as the business process this db is to support. So far I can identify: companies, annual data. Otherwise, don't really know what this database is about. Fields like DrawnDebt, Available, Cash make me think there should be a table of transactions to document the inflow and outflow of assets from which balances would be calculated.

[Bschmale]

I agree, I am a long way from understanding all of this stuff. I am currently going through and trying to normalize my data like the examples you have provided as well as those online.

The goal of this database is to provide a snapshot of how well publicly traded companies are performing financially as well as operationally.

I appreciate your help and may check back when I have everything closer to a normalized structure.

[orange]

bschmale,

You will learn a lot about database and table design, relationships and Normalization by working through this free tutorial from RogersAccessLibrary.
Spend an hour or so on it and you will benefit greatly. The things you learn can be applied to any database project and any dbms software.

Good luck.