difficulty with a relationship/subform - microsoft.public.access.forms

Hi all, I am new to Access 2007 and need to create a very simple database to track a location and any activity(s) that happen there. I am guessing that I need a table to store the name, address and phone number of the address then another table to track the date of activity, nature of the activity and a memo field. I would like the information from the first table to be on top of a form and the info from the second table to be a subform where I can see all of the activities. I know how to make the tables I just dont know how to join them so it filters the activities shown on the form to the address shown on the form. Any and all help would be greatly appreciated AJ Brofman Chief Fire Marshal Lake Grove, NY

One of the true beauties of Access and the use of bound forms is the automatic linkage that the Wizards perform when building forms/subforms. All you need to do to make it easy id create the form and subform, then open the form in Design View and use the subform tool in the design toolbox to add a subform. The wizard will take you through the rest. AAMOF, if you don't have a subform, the wizard will assist in creating one for you. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "firecop1" <ajb5540@optonline.net> wrote in message news:5BA08EAE-3B67-4A26-9938-21BF63ED7C33@microsoft.com... > Hi all, > I am new to Access 2007 and need to create a very simple database to track > a location and any activity(s) that happen there. > > I am guessing that I need a table to store the name, address and phone > number of the address then another table to track the date of activity, > nature of the activity and a memo field. I would like the information from > the first table to be on top of a form and the info from the second table > to be a subform where I can see all of the activities. > > I know how to make the tables I just dont know how to join them so it > filters the activities shown on the form to the address shown on the form. > > Any and all help would be greatly appreciated > > AJ Brofman > Chief Fire Marshal > Lake Grove, NY

So I should create my two tables. What is the field that I will use to link them (I am guessing it is a one to many relationship). Then I create two forms-one for each table? Then I use the wizard to add the subform? I know this is very simple but I am not that good yet so I need a bit more explaination if you wouldnt mind Andy "Arvin Meyer [MVP]" <a@m.com> wrote in message news:enP1GCRtHHA.912@TK2MSFTNGP03.phx.gbl... > One of the true beauties of Access and the use of bound forms is the > automatic linkage that the Wizards perform when building forms/subforms. > All you need to do to make it easy id create the form and subform, then > open the form in Design View and use the subform tool in the design > toolbox to add a subform. The wizard will take you through the rest. > AAMOF, if you don't have a subform, the wizard will assist in creating one > for you. > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.mvps.org/access > http://www.accessmvp.com > > "firecop1" <ajb5540@optonline.net> wrote in message > news:5BA08EAE-3B67-4A26-9938-21BF63ED7C33@microsoft.com... >> Hi all, >> I am new to Access 2007 and need to create a very simple database to >> track a location and any activity(s) that happen there. >> >> I am guessing that I need a table to store the name, address and phone >> number of the address then another table to track the date of activity, >> nature of the activity and a memo field. I would like the information >> from the first table to be on top of a form and the info from the second >> table to be a subform where I can see all of the activities. >> >> I know how to make the tables I just dont know how to join them so it >> filters the activities shown on the form to the address shown on the >> form. >> >> Any and all help would be greatly appreciated >> >> AJ Brofman >> Chief Fire Marshal >> Lake Grove, NY > >

Yes that is exactly correct. Use the Primary Key from the One side of the relationship as a Foreign Key on the many side. There is a demo with the Orders/Order Details tables and forms in the Northwind sample database that came with Access: Help >>> Sample Databases >>> Northwind.mdb -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "firecop1" <ajb5540@optonline.net> wrote in message news:9162A2ED-DB96-4703-A53A-2877CB99DB77@microsoft.com... > So I should create my two tables. What is the field that I will use to > link them (I am guessing it is a one to many relationship). > Then I create two forms-one for each table? > Then I use the wizard to add the subform? > > I know this is very simple but I am not that good yet so I need a bit more > explaination if you wouldnt mind > > Andy > > "Arvin Meyer [MVP]" <a@m.com> wrote in message > news:enP1GCRtHHA.912@TK2MSFTNGP03.phx.gbl... >> One of the true beauties of Access and the use of bound forms is the >> automatic linkage that the Wizards perform when building forms/subforms. >> All you need to do to make it easy id create the form and subform, then >> open the form in Design View and use the subform tool in the design >> toolbox to add a subform. The wizard will take you through the rest. >> AAMOF, if you don't have a subform, the wizard will assist in creating >> one for you. >> -- >> Arvin Meyer, MCP, MVP >> http://www.datastrat.com >> http://www.mvps.org/access >> http://www.accessmvp.com >> >> "firecop1" <ajb5540@optonline.net> wrote in message >> news:5BA08EAE-3B67-4A26-9938-21BF63ED7C33@microsoft.com... >>> Hi all, >>> I am new to Access 2007 and need to create a very simple database to >>> track a location and any activity(s) that happen there. >>> >>> I am guessing that I need a table to store the name, address and phone >>> number of the address then another table to track the date of activity, >>> nature of the activity and a memo field. I would like the information >>> from the first table to be on top of a form and the info from the second >>> table to be a subform where I can see all of the activities. >>> >>> I know how to make the tables I just dont know how to join them so it >>> filters the activities shown on the form to the address shown on the >>> form. >>> >>> Any and all help would be greatly appreciated >>> >>> AJ Brofman >>> Chief Fire Marshal >>> Lake Grove, NY >> >> >

Thank you Arvin, it worked like a charm....a very nice feature in Access. Take care Andy "Arvin Meyer [MVP]" <a@m.com> wrote in message news:egoXxgbtHHA.1728@TK2MSFTNGP06.phx.gbl... > Yes that is exactly correct. Use the Primary Key from the One side of the > relationship as a Foreign Key on the many side. > > There is a demo with the Orders/Order Details tables and forms in the > Northwind sample database that came with Access: > > Help >>> Sample Databases >>> Northwind.mdb > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.mvps.org/access > http://www.accessmvp.com > > "firecop1" <ajb5540@optonline.net> wrote in message > news:9162A2ED-DB96-4703-A53A-2877CB99DB77@microsoft.com... >> So I should create my two tables. What is the field that I will use to >> link them (I am guessing it is a one to many relationship). >> Then I create two forms-one for each table? >> Then I use the wizard to add the subform? >> >> I know this is very simple but I am not that good yet so I need a bit >> more explaination if you wouldnt mind >> >> Andy >> >> "Arvin Meyer [MVP]" <a@m.com> wrote in message >> news:enP1GCRtHHA.912@TK2MSFTNGP03.phx.gbl... >>> One of the true beauties of Access and the use of bound forms is the >>> automatic linkage that the Wizards perform when building forms/subforms. >>> All you need to do to make it easy id create the form and subform, then >>> open the form in Design View and use the subform tool in the design >>> toolbox to add a subform. The wizard will take you through the rest. >>> AAMOF, if you don't have a subform, the wizard will assist in creating >>> one for you. >>> -- >>> Arvin Meyer, MCP, MVP >>> http://www.datastrat.com >>> http://www.mvps.org/access >>> http://www.accessmvp.com >>> >>> "firecop1" <ajb5540@optonline.net> wrote in message >>> news:5BA08EAE-3B67-4A26-9938-21BF63ED7C33@microsoft.com... >>>> Hi all, >>>> I am new to Access 2007 and need to create a very simple database to >>>> track a location and any activity(s) that happen there. >>>> >>>> I am guessing that I need a table to store the name, address and phone >>>> number of the address then another table to track the date of activity, >>>> nature of the activity and a memo field. I would like the information >>>> from the first table to be on top of a form and the info from the >>>> second table to be a subform where I can see all of the activities. >>>> >>>> I know how to make the tables I just dont know how to join them so it >>>> filters the activities shown on the form to the address shown on the >>>> form. >>>> >>>> Any and all help would be greatly appreciated >>>> >>>> AJ Brofman >>>> Chief Fire Marshal >>>> Lake Grove, NY >>> >>> >> > >

Similiar Articles:

Performance issue with subform - microsoft.public.access.forms ...
I have a performance problem with a form called Licence containing a subform. The form uses the data contained in 3 tables based on an m:n relationship.

Can't Enter New Record in Subform - microsoft.public.access ...
This is certainly a one to many relationship, and it displays existing ... The problem isn't the code to enter the new record into the subform. That works fine. The problem ...

A newbie paradox: is this a PK-FK (relationship) problem, or ...
all fields in editable subform behave as locked even though they a ... A newbie paradox: is this a PK-FK (relationship) problem, or ... understanding what you mean and ...

Subform not allowing data entry - microsoft.public.access.forms ...
... used the forms wizard to create the f_alarm_info subform, and f_POC_info main form. The problem I'm ... to my google docs which has pdf's of the tables' relationships ...

Subform linked to form - ID number to come into subform ...
I am an Access novice with no VB experience. I have created a form (Software Register) with a subform (Licence No). The related tables have a relationship defined ...

Problem with color in subform - microsoft.public.access.forms ...
Access SubForm Problem - Wrox Programmer Forums I'm struggling with the same problem with a subform on a form..... I'm using the conditional formatting for background ...

Can't delete record in subform - microsoft.public.access ...
... hang.but when I remove subform B from my form, I can delete. the relationship between these 2 table/subform are cascade update & delete. Any idea to solve this problem?

Clear the subform - microsoft.public.access.forms
The RecordSource of the parent form (a query) has a 1-many relationship with the RecordSource of the subform (a table). The data in the parent is not intended to be ...

If No Records in Subform, Parent Form Record doesn't display ...
If I don't have any records in a subform, the record in ... So this that isn't the problem. Is there somewhere to establish the relationship between the parent and child?

Creating a customer record in a child table - microsoft.public ...
If you set one-to-many relationship between tblCustomer and tblCallInfo ... I did remove the datepicker active X from the subform, hsowever the main problem remains ...

Create a form that contains a subform (a one-to-many form ...
Access also tries to link the subform to the main form, based on the relationships that have been defined in your database. Repeat this step to add any additional ...

Create a subform - Access - Office.com
... in the matching field or fields of many records in the related table.) with the first subform, and the first subform has a one-to-many relationship with the second subform ...

Microsoft Access: MainForm - SubForm Problem. - database.itags.org
At the moment I am having a problem with relationships and updating tables. The following is a production run. Table1 -- MainFormTable1 -- Basic Table Record Table2 ...

How to Add a Subform in Access 2007 | eHow.com
The combination of form and subform may be called a ... commonly used to show data with a one-to-many relationship. The following steps will show how to add a subform in ...

Querying a SubForm with a MANY to MANY relationship with MainForm ...
Querying a SubForm with a MANY to MANY relationship with MainForm ... also notice this problem with a 1 to Many relationship (the "many" portion being the subform)

7/9/2012 2:16:17 PM