Main Form with Subforms

[SaM]

I am creating a form with the main form at the top - data comes from a
specific query within the database so that I am able to sort and place
filters within the query to get the data I want, the main record source is
Project ID..

The first subform is side by side with the second subform. The 1st has data
that comes from a table within the database, and the child link key is the
project number that links to the master project ID.

The second subform again comes from a different table within the database,
the child is project Number, to Master of Project ID.

The idea is the main form is a continuous form, the two subs are single and
data entry capable. What I want this to do, but seem unable to do is to be
able to click on a record in the main form and have it display in the two
subforms...but for some reason this is not doing what I need it to do.

Please, Please...does anyone have any suggestions?

 

[golfinray]

How are your tables and queries related? Are you saying project ID is the
primary key in the main table and the others use project number? Are the
project numbers the same as the project id? Do you have relationships defined
on these? Do you think it will be a one-to many with the mainform being the
one side and subforms being the many side? Since you want two subforms, is
there data in one table and one query that both relate to the main table? I
am asking all these questions because if all your relationships and keys were
set properly, it would probably work.

 

[SaM]

Milton,

Thanks for responding first of all.

Project ID is primary, which is in the query used in the Main form, and
Project Number (used from two different tables in same database) used in
Subforms, Project Number being the same as Project ID. I don't know why they
named it differently in that query, but...regardless. They are one and the
same.

I guess maybe I don't have relationships defined - I thought that by
selecting the one to be master (in properties in each form) and the one as
child, this would define the relationship. Is this not correct?

To be perfectly honest, all the information in the Query pertains to all the
information in the tables - that is to say they all relate in some form or
another. Project ID, Project Number, Project Description...these are in both
the query and both tables. The main form lists several (continuous) projects,
when you click on one, it would display (supposedly) in both subforms. The
first subform would have more details about the project, the second subform I
can enter in approved dollar amounts or take away funds from the project. The
second form has less info.

 

[SaM]

I thought it would be easiest if I displayed fields in the tables and query:
Query1 – Main Form
Project ID
Project Description
Project Type
Original Plan
RLT Approved
YTD
Current Plan
Current Forecast

Table1 – Subform1
Project ID
Project Description
Project Type
Project Status
Venture
Initiative
YR_2010
YR_2011
YR_2012
YR_2013
YR_2014
Notes
Project_MGR_NM
Owner_NM
Sponsor_NM
YR_2015
YR_2016

Table2 - Subform2
Project Number
Description
Project Type
2010
Notes
RLT Approved Change
Approved Date

Will that help any?

 

[John W. Vinson]

Comments and concerns inline.

I thought it would be easiest if I displayed fields in the tables and query:
Query1 – Main Form
Project ID
Project Description
Project Type
Original Plan
RLT Approved
YTD

If this is a Year To Date sum of (something) then the field should simply NOT
EXIST. Instead it should be calculated on the fly in a Query.

Current Plan
Current Forecast

Table1 – Subform1

I'm confused. What is the relationship between Query1 and Table1? Tables are
*fundamental* - queries are based on Tables, Forms are based on Queries. I
worry that you might be building your house upside down...!

Project ID
Project Description
Project Type
Project Status
Venture
Initiative
YR_2010
YR_2011
YR_2012
YR_2013
YR_2014

"Fields are expensive, records are cheap". What will you do in 2015? Redesign
all your tables, all your forms, all your queries, all your reports? go out of
business??

If each Project has multiple values for different years, then you should -
must, I'd say! - have *two tables* in a one to many relationship. Each year's
data would have a ProjectID foreign key link to Table1 (Projects, I'd call it;
meaningful names make life much easier), with one record per year. You would
then be able to use Totals queries or DSum() functions to add up amounts from
the multiple years.

Notes
Project_MGR_NM
Owner_NM
Sponsor_NM

I'd really suggest a table of People, with fields PersonID, LastName,
FirstName, and other biographical data; this table should have number fields
as links to the PersonID.

YR_2015
YR_2016

Yep. There you go... you ran out of years and had to fix your table. That is
NOT necessary if you have a properly normalized design!

Table2 - Subform2
Project Number
Description
Project Type
2010
Notes
RLT Approved Change
Approved Date

Will that help any?

Is 2010 a fieldname? Is Project Number a link to Table1.Project ID or an
unrelated number? More info please!

Meaningful table names, and a brief description of what Entity Type (real-life
person, thing or event) each table represents would be very helpful.

 

[SaM]

Mr. John W. Vinson - I apologize - assuming you have worked in databases long
enough that even you should know that most generally you are not in the
ground up process of creating or building a database - this is one I
inheritated 8 months ago and have been instructed not to spend much time
reworking the tables and queries, but simply work with what I have as this
database will be replaced by an oracle database in the very near future. So,
I am working with what I have.

I have a generalized make table query that dates the years - future 1,
future 2, so on and so forth.

If you would have read on further, some of the questions you asked would
have been answered - I realize I am the one asking for help, but quite
frankly this shouldn't be that big of a deal - I had it working once, renamed
a query and replaced it and now I cannot get it to work.

As most of this is pertaining to Projects and the management of such, the
title of each field is pretty descriptive I would think. The Year fields are
what the projects forecast for spending on those projects, each project has
an owner, a project manager, and a sponsor, each project has a description,
project type - capital or expense. Each project has an original approved
amount of spending - Current Plan is what is currently approved, Current
forecast is what they think they will spend that year, YTD is what they have
spent that year and then we go to meetings and funds are either added or
shaved off and this is the RLT Approved field. The notes are just that,
notes on the project, approved date is the date the new data entered was
approved. Very simple.

Susan M.
Project Controls Data Coordinator