Development Tips Needed

[emblic]

Hi all,

Ok here it goes. I've been working on this database on and off the last two months but run into walls the whole time. I've got a feeling that it actually is simple but my problem lies at the very beginning in defining my tables and relationships. I can't decide between having a flat structure or a relational database.

Problem Statement:

I want to compile a database that monitors the returns and other information of investment funds over time. Each month data about each fund is entered into the database.

The information about each fund is categorized as follows:
1. Returns
2. Asset Composition
3. Market Value of fund

1 and 2 has further subdivisions but thats why we use Access...

I started of by designing a table FundInfo. This table contains all the funds I want to monitor with following fields: FundID, FundName, Type, RiskType, Manager and Region. My primary key here is FundID and has datatype autonumber.

Since the data for each fund changes monthly, I then designed various different tables all with following multifield key: FundID and Date since such a record will be unique to each fund. The FundID (data type: text) here is obtained via a query where you choose the fundname but the id is stored. The relationships are then as follows: one to many between FundInfo and all other tables.

Things are getting complicated at this stage because when I start using queries I keep getting an error: Type Mismatch. It is here that I suspect my relationships are wrong.

Any comments, tips or just interest would be appreciated as I am feeling a bit frustrated on this one. "Access for Dummies" is a great help but I need some human intervention.

At the end of the day I want to use the database to compile a report on all the various funds. It has to include graphs etc.

Waiting in anticipation

 

[snyderman]

mismatch

em, your base problem may be as simple as your data types are different. the first thing I would do to de-bug, is print-screen every table in design-view, and compare each field. I keep a "diary" as I build tables, so I can keep track of the field types and lengths.

 

[neileg]

Not enough info. There could be lots of reasons for getting a 'type Mismatch' error.

Could you compact, zip and post yourt database here. Many people prefer A97 format.

 

[emblic]

Thanks Neil,

I sorted out the mismatch error - had to change the fundid form text to number. I'm attaching the database just for any recommendations from your side. Any data contained so far is just for testing purposes.

 

[neileg]

It's a bit hard to follow what you db is meant to do with so little in it, just a bunch of tables. I'd make the following observations, though.

1) You seem to have a lot of tables that will contain data depending on the type of investment fund. Thus your table for domestic composition will be empty if it is an international fund. I don't know enough about the realities of the data you are trying to model to know if this is correct.

2) You should adopt a naming convention. Eg prefix your table names with Tbl, queries with Qry, etc. There are established naming schemes that you could adopt. Try a search on here.

Good luck