Multiple Tabled DB 1 form for entry

[kevin007]

Hi. Newbie Alert!



Im building my first form. I have a DB of economic indicators and my sales data. The idea is to do a correlation analysis using the data in the tables of external factors to sales. I have 10 years worth of sales data in a tbale. Then I have 10 years worth of economic indicator data in other tables. Thes other tables are catagoried. I.E. tblLabour has unemployment rates, and employment rates, tblWealthIncome has EI recipients, Average Weekly earnings etc.

My structure is simple and not very normalized but its what I have (remember im new) I have a central table called tbldates with all the months in a column (jan 1996, feb 1996, mar 1996 up until jan 2025. beside all this is a date ID. All my tables are related to tblDates through the date ID. Most of this data is monthly, I have one table with yearly data, and one table with quarterly data.

Now to the point. I just started creating a form. I have frmLabour built with all the feilds I need.

I simply need to add the rest of my tables to the form. I need feilds for all my other indicators that are located in the various category tables to all appear on one form. As new stats come out each month (or quarter or year) I would like my data entry clerk to be able to access 1 form and do all the entries there.

This should be simple. Please help. Im new!~

I attached my table structure in case youre interested.

Thanks

[NTC]

in the most general terms; as you note that all the tables are normalized - meaning there is a valid cross referencing field between them ; then decide which table is the highest 'parent' set of information - and make that the source of your first main form. Then insert the other tables as subforms. The wizard will launch and auto set up the cross referencing fields.

[kevin007]

Hi thanks for the reply. I'm not sure about the "parent" table you suggest. Could you please provide a bit of detail. Im a little slow with this stuff. So, given my structure with tblDates as the central table that all the oterh tables are related to...would this be the "parent" table? All my other tables are the same really. The have a feild of DateID and a feild of values for each indicator in them. Some of them have more feilds than others (tblWealth has 6 economic indicators in it and tbllabout has 4) but that doesn't really make them "parent tables so to speak, they are just larger) Given this purpose and this structure would you be able to recommend which table to use as my starting point for the main form (parent table) Should this be my internal sales data perhaps, or my largest table? Or table dates mabey?

Also once this is built would you be able to point me in the right direction as to how to create "subforms" Ive never done this.

THanks again.

[NTC]

well definitely purchase an Access textbook in the version you have. Easily found at Amazon or a big box bookstore. Making subforms is a core function they will all cover.

like a root/tree hierarchy the parent table is the highest most level - all tables cross reference to it. Some may cross reference only to other lower level tables and not directly to the parent table. This is not just esoteric db theory - it is also common sense and an important thing to get straight in your head - as to what relates to what.... use pen/paper to sketch it out.

so pragmatically when you go to record 123 - all the data related to it can also show up each in their own subform.