Newbie - form from multiple tables

[Sean Davis]

Hi everyone,

I've never taken an Access course,but have taught myself over the
years a lot of the backend and am very comfortable working just within
the raw data and tables, building queries and writing SQL, pushing
data to excel, blah blah blah. Bottom line, I enjoy Access a lot and
choose it for many of my needs, but I have no idea how to use forms.
Like, at all beyond the Wizard.

So, first. I'm using Access 2007.

My data:
Table: Students
Table: Finance
Query: Balance

I need to make this database useable for people who are not
comfortable with the "insides" of Access and just give a form for data
entry and reviewing what's happening.

Ideally, the form will include all data from the table Students:

Name - Billy-Bob
Age - 25
Sex - M
Hometown - Kiswaga
Sponsorship - Y
GradYear - 2008

I've done this. No problem. However, I want to include data from the
table Finance in there as well, either on a new tab of the form, or
right alongside the student info.

The data within the Finance table contains three basic columns - Name,
Income, and Expense (well, ok, there's more like dates and notes, but
I'll leave that out). So for Billy-Bob, I'd like to return this:

Billy-Bob -- $100 -- ___
Billy-Bob -- ___ -- $50
Billy-Bob -- ___ -- $40

.... again, either on the same page or on a seperate tab, so that I can
enter data in for either Income or Expenses. I'm not sure if this is
possible as I would like the ability to add a new record for every new
expense, but also have the ability to review all income and expenses
(all records essentially) for the student in question at once in a
format as stated above.

Finally, I'd like to be able to put, somewhere, the results from the
query Balance. Balance is just a simple total for each student
containing Name, SumofIncome, SumofExpense, and BalanceRemaining.

Billy-Bob -- $100 -- $90 -- $10

Ideally this would be placed under the all-record view from the
Finance table (listed just above). I've experimented with creating a
*report* from the Finance table and this actually returns exactly what
I want, but I don't think I can include individual student reports on
the form.

Keep in mind, I have a ton of experience with Crystal Reports XI and
am having a tough time shaking the idea of what can or can't be done
from that program as I move over to Access. I know, strange that I
know database and Crystal yet can't do a simple form. I'm ashamed, but
I've just taught myself with Access.

Thanks for any and all assistance. Sorry this post is so huge. I'd
also very gladly take any links to resources for learning forms that
cover the basics of what I'm asking here. Consider me a blank page.
-Sean

 

[BruceM via AccessMonster.com]

Typically you would use a student ID rather than the student name, as names
are subject to change. You would have a main table for students:

tblStudent
StudentID (primary key, or PK)
FirstName
LastName
etc.

tblFinance
FinanceID (PK)
StudentID (link to tblStudent)
Item
etc.

Go to Tools >> Relationships. Add both tables. Drag StudentID from one
table to another. When the dialog box appears, click Enforce Referential
Integrity. Note that StudentID must be the same data type in both tables.
However, if it is Autonumber in tblStudent, make it Number (Long Integer) in
tblFinance.

Create a form based on tblStudent. Create another form (default view
Continuous, as a suggestion) based on tblFinance. On tblStudent, add a
subform control from the toolbox. Set its Source Object to the Finance form,
and set its Link Child and Link Master fields to StudentID if they are not
already. Now each Student record in the main form can have any number of
related Finance records in the subform. The Link Master and Link Child field
selection means StudentID is entered automatically in each Finance record.

This is very quick and basic. For a more thorough look at how relational
databases are put together, and how to use Access in particular, see this
fine tutorial:
http://allenbrowne.com/casu-22.html