A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple Records



 
 
Thread Tools Display Modes
  #21  
Old November 11th, 2008, 05:26 PM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default Multiple Records

It could be the indexing in the Capacities table is wrong. The primary key
should be a composite one on all three columns, PersonnelID, CompanyName and
Capacity. In table design view you select all there fields together and then
right click and select Primary Key from the shortcut menu. The key symbol
should thenshow against all three fields. The PersonnelID and CompanyName
fields, as foreign keys, should each be indexed non-uniquely. This will show
in the properties sheet of each field in table design view as 'duplicates
allowed' in the index property.

Its theoretically possible that there could be other indexes set, which you
can check by selecting View | Indexes from the menu bar while in table design
view. If any of the fields are individually indexed uniquely then this would
prevent any rows being inserted into another row if the same value already
exists in the field; if a PersonnelID value has already been entered for
somebody as a director or shareholder for instance, and the PersonnelID is
indexed uniquely (no duplicates) then it would prevent the same person being
added as an officer.

I'm dubious about this being the problem, however, as it would very
coincidental if all officers had already been entered as directors or
shareholders. I'd be more inclined to suspect the data as the first culprit.
Are you sure that the officers are showing correctly as such in the source
tblMyExcelStuff table? If so have they been appended correctly to the
Personnel table? They'll just show as names in personnel of course, so you
really need to lock for people who are officers but not also shareholders or
directors to be sure they've all been appended correctly.

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Okay, I am going to pull my hair out...I have checked everything I can
regarding the append query for my Officers and I get the same error as I
previously mentioned. I run it and it populates the query but it won't let
me append to the Capacities Table....I have checked the format, the spelling
on both the tblMyExcelStuff and even compared and rewrote the code but still
nothing...why is it that the other 2 went through and are in the Capacities
table but this one is going to drive me nuts? Any suggestions?


Ads
  #22  
Old November 11th, 2008, 09:17 PM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Oh no...I didn't set the Primary key and the 2 foreign keys for the
capacities table...actually, I didn't realize I had to do that. So I guess I
should redo all of my queries and such and try again. Do you have time to
let me know where my primary keys and foreign keys should be and list the
relationships to make sure that I have them right? At this point, I am
doubting that I even had them right...boy I feel like a real newbie. Thank
you so much for your patience and feedback with assistance. I have been
following your instructions and they have been working but I don't want to
have to redo again if I can avoid it. I do have a hard time with
relationships and now obviously designating the proper Primary and Foreign
Keys are an issue with me.
  #23  
Old November 12th, 2008, 01:40 AM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default Multiple Records

Not having created the keys or relationships shouldn't have caused the
problem with officers, so doing so might not solve it unless you've
inadvertently created an incorrect primary key or unique index in Capacities.
Nevertheless it should be done. You should not have to run the queries
again though; the data which has been inserted into the tables should not
conflict with the keys or relationships. Incidentally a foreign key is not
set in the table design, it’s a by-product of creating a relationship.
Taking it table by table:

1. Companies

Set the Company name field as the primary key by selecting it in table
design by clicking on the field selector (the little square on the far left
of the field), right clicking and then selecting primary key from the
shortcut menu.

If there are any other fields, e.g. Incorporation #, in this table whose
values will be unique in the table, i.e. the same value can't appear in more
than one row the index the field uniquely by selecting Yes (No Duplicates) as
its Index property in its properties sheet.

2. Personnel

Set the PersonelID field as the primary key in the same way as above.

3. Capacities

The primary key of this table is all the fields in combination, so select
all three by clicking on the field selector of each in turn while holding
down the Ctrl key, or by clicking a dragging down over all three field
selectors. The right click and select primary key. You should then see the
key symbol against all the fields.

The PersonnelID and Company name fields in this table will both be used as
foreign keys, so index each of them non-uniquely by selecting Yes (Duplicates
OK) as the Index property of each. The indexes will improve performance
when the tables are joined in queries or linked in a form/subform.

Relationships:

To create the relationships open the relationships window. With the mouse
pointer inside the window right click and select Show table. Add the three
tables to the window and move them so that they are in a line with Companies
on the left, Personnel on the right and Capacities in the middle.

With the mouse click and drag from Company Name in the Companies table to
Companies Name in the Capacities table. The 'Edit Relationship' dialogue
will open; it should say 'one-to-many' at the bottom. In this check the
'Enforce referential integrity' check box and then click the Create button.
It should create the relationship, but if you get an error message then there
is something in the data which is preventing referential integrity being
enforced. This should not happen, but if it does uncheck the 'Enforce
referential integrity' check box and then create the relationship until
you've sorted out what the problem with the data is (it means that there is a
row in Capacities without a match in Companies).

Then do exactly the same between Personnel and Capacities by clicking and
dragging from PesonnelID in Personnel to PesonnelID in Capacities.

You'll see now how the Capacities table has resolved the many-to-many
relationship which exists between Companies and Personnel into two
one-to-many relationships. This is always how a many-to-many relationship is
represented, never directly between two tables. Tables like Capacities which
do this are sometimes referred to as 'junction' tables or some other similar
term, but that's just a folksy way of saying that they are modelling a
relationship between other tables, usually a many-to-many relationship, but
in some special circumstances it can be a one-to-many relationship.

With the keys and relationships set up as above your 'logical model' or
'schema' is now securely set up so that you can't enter invalid or
inconsistent data. However, I'm doubtful that it will solve the problem of
appending the officers to Capacities. As I said before one explanation of
that would be the presence of incorrect unique indexes, not the lack of
indexing.

Let me know how you get on with creating the keys, indexes and
relationships, and we'll take it from there.

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Oh no...I didn't set the Primary key and the 2 foreign keys for the
capacities table...actually, I didn't realize I had to do that. So I guess I
should redo all of my queries and such and try again. Do you have time to
let me know where my primary keys and foreign keys should be and list the
relationships to make sure that I have them right? At this point, I am
doubting that I even had them right...boy I feel like a real newbie. Thank
you so much for your patience and feedback with assistance. I have been
following your instructions and they have been working but I don't want to
have to redo again if I can avoid it. I do have a hard time with
relationships and now obviously designating the proper Primary and Foreign
Keys are an issue with me.


  #24  
Old November 12th, 2008, 05:45 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Ken...you are a genius! Obviously I was doing something terribly wrong with
the Primary and Foreign keys because your guided assistance was
successful!!!! I now have the 3 tables that we were trying to achieve with
the Capacities Table showing the Officers, Shareholders and Directors
together! So now I will attempt to make my forms as per your instruction
previously. Is there anything I should do or tweak before setting out on the
form tasks? I really really appreciate your assistance and especially your
explanations..it starts to make a little more sense. I don't do code but
with all of this, I was at least able to understand what we were trying to
achieve. I will copy your instructions now for my forms and will let you
know. THANKS AGAIN!!!
  #25  
Old November 12th, 2008, 08:06 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but
I didnt see where I had to put "ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

From the instructions you gave, I think I missed an important step here.
  #26  
Old November 12th, 2008, 05:58 PM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default Multiple Records

Take another look at my original reply. You'll see that what you need is a
Company form and then 3 subforms within it so the parent Company form doesn't
include the Personnel or Capacities table; its based solely on the Company
table. Rather than simply making the Companies table the form's
RecordSource, however, its better to first create a query which sorts the
companies in name order and then use the query as the RecordSource. The
query is a very simple one:

SELECT *
FROM [Companies]
ORDER BY [CompanyName];

Save the query, as qryCompanies say, and then base a form on it. You can
use the form wizard to create the form and then amend it later when you add
the subforms. The form must be in single form view, though.

You now need to create three separate subforms, but as they'll be identical
apart from their RecordSource properties you can just create one and then
copy and paste it under new names twice to create the others. You just have
to change the RecordSource property of the two copies.

First create the three queries on which the subforms will be based:

For directors:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Director";

For shareholders:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Shareholder";

And for officers:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Officer";

Now create the first form which you'll use as the directors subform. This
will be a form in continuous form view based on the first of the above
queries.

Each subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:

ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Each subform should also have a text box bound to the Capacity field, but
with its Visible property set to False (No) and its DefaultValue property set
to Director, Shareholder or Officer as appropriate in each case.

So if you use the form wizard to create the first subform include the
PersonnelID and Capacity fields, but then hide the latter by setting its
Visible property to False (No) in form design view.

In answer to your question you'll find ControlSource as a property of the
combo box on the data tab of its properties sheet.

Once you've created the first subform copy and paste it twice, giving it a
different name each time, so you might end up with three identical forms
called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names
you use is entirely your choice, but it helps if the names are meaningful).
Open each of the two copies in design view and change the form's RecordSource
property to the appropriate one of the three queries.

You can now embed the three forms in the original Company form as subforms.
Open the Company form in design view and size the window so there's some
spare space and then drag the edge of the form design area to create some
empty space on it to place the subforms. You can add each subform either by
adding a subform control from the toolbox and setting the SourceObject
property to one of the three subforms you created, or by dragging and
dropping the source forms from the database window onto the design area of
the company form.

Move the subforms around to position them on the main parent form how you
want and size them as appropriate. You'll probably find that putting the
three side by side at the bottom makes sense.

To link with the parent form the LinkMasterFields and LinkChildFields
properties of each of the three subform controls should be CompanyName,
though you'll very likely find that Access has already done this for you on
the basis of the relationships you created.

You should now find that as you moved from company to company through the
main form the subforms will change to show the directors, shareholders and
officers for the current company.

Finally I explained in my first reply how to include unbound combo boxes on
the main company form to search for a company or person, but I'll repeat what
I said (make sure the field an table names in the code match what you've used)

To search for a company you can use the built in 'Find' facility or you can
add an unbound combo box to the main form with a RowSource of:

SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName];

and with the following code in its AfterUpdate event procedu

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
..FindFirst "[CompanyName] = """ & ctrl & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

To find a person, whatever their capacity add another unbound combo box to
the main form set up in the same way as those in the subforms:

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In its AfterUpdate event procedure put the following code:

Dim strFilter As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strFilter = "[CompanyName] IN" & _
"(SELECT [CompanyName] " & _
"FROM [Capacities] " & _
"WHERE [PersonnelID] = " & ctrl & ")"
Me.Filter = strFilter
Me.FilterOn = True

Note that, unlike the combo box for finding a company, this filters the
form. This is because more than one company might be associated with the
selected person. You can clear the filter using the built in button on the
toolbar, or you can add a 'Show All' button to the main form with the
following in its Click event procedu

Me.FilterOn = False

One thing you might like to do before you do any of this is check that the
tables, now that you've got all the data in, do give the correct results when
joined. You can do this with a query which will list all companies, with
their directors, officers, shareholders. You don't have to save the query if
you don't wish to, just create it in SQL view and then switch to datasheet
view to see the results:

SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName]
FROM [Companies], [Capacities], [Personnel]
WHERE [Companies].[CompanyName] = [Capacities].[CompanyName]
AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID]
ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname];

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but
I didnt see where I had to put "ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

From the instructions you gave, I think I missed an important step here.


  #27  
Old November 14th, 2008, 05:47 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Hi Ken

I have created the 3 queries for my subforms but am still a little confused.
I made the 3 forms using the wizard and did as you said and changed the
RecordSource accordingly. My main form is in Single view. I have made the
PersonnelID not visible but I am stuck on something...your comment "Each
subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:
ControlSource: PersonnelID " you are talking about the subform and then you
talk about a combo box. Am I making a combo box in the subform? I made a
combo box on the main form to see if that is what you were referring to but
there was no option for the ControlSource being PersonnelID only CompanyName,
Incorporation, DateOfIncorporation and SDCFileNumber ( Companies Table). So
I am not sure where I am adding the code for the RowSource or selecting the
ControlSource as PersonnelID ? I hope this makes sense to you

"Ken Sheridan" wrote:

Take another look at my original reply. You'll see that what you need is a
Company form and then 3 subforms within it so the parent Company form doesn't
include the Personnel or Capacities table; its based solely on the Company
table. Rather than simply making the Companies table the form's
RecordSource, however, its better to first create a query which sorts the
companies in name order and then use the query as the RecordSource. The
query is a very simple one:

SELECT *
FROM [Companies]
ORDER BY [CompanyName];

Save the query, as qryCompanies say, and then base a form on it. You can
use the form wizard to create the form and then amend it later when you add
the subforms. The form must be in single form view, though.

You now need to create three separate subforms, but as they'll be identical
apart from their RecordSource properties you can just create one and then
copy and paste it under new names twice to create the others. You just have
to change the RecordSource property of the two copies.

First create the three queries on which the subforms will be based:

For directors:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Director";

For shareholders:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Shareholder";

And for officers:

SELECT [CompanyName], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Officer";

Now create the first form which you'll use as the directors subform. This
will be a form in continuous form view based on the first of the above
queries.

Each subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:

ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Each subform should also have a text box bound to the Capacity field, but
with its Visible property set to False (No) and its DefaultValue property set
to Director, Shareholder or Officer as appropriate in each case.

So if you use the form wizard to create the first subform include the
PersonnelID and Capacity fields, but then hide the latter by setting its
Visible property to False (No) in form design view.

In answer to your question you'll find ControlSource as a property of the
combo box on the data tab of its properties sheet.

Once you've created the first subform copy and paste it twice, giving it a
different name each time, so you might end up with three identical forms
called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names
you use is entirely your choice, but it helps if the names are meaningful).
Open each of the two copies in design view and change the form's RecordSource
property to the appropriate one of the three queries.

You can now embed the three forms in the original Company form as subforms.
Open the Company form in design view and size the window so there's some
spare space and then drag the edge of the form design area to create some
empty space on it to place the subforms. You can add each subform either by
adding a subform control from the toolbox and setting the SourceObject
property to one of the three subforms you created, or by dragging and
dropping the source forms from the database window onto the design area of
the company form.

Move the subforms around to position them on the main parent form how you
want and size them as appropriate. You'll probably find that putting the
three side by side at the bottom makes sense.

To link with the parent form the LinkMasterFields and LinkChildFields
properties of each of the three subform controls should be CompanyName,
though you'll very likely find that Access has already done this for you on
the basis of the relationships you created.

You should now find that as you moved from company to company through the
main form the subforms will change to show the directors, shareholders and
officers for the current company.

Finally I explained in my first reply how to include unbound combo boxes on
the main company form to search for a company or person, but I'll repeat what
I said (make sure the field an table names in the code match what you've used)

To search for a company you can use the built in 'Find' facility or you can
add an unbound combo box to the main form with a RowSource of:

SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName];

and with the following code in its AfterUpdate event procedu

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "[CompanyName] = """ & ctrl & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

To find a person, whatever their capacity add another unbound combo box to
the main form set up in the same way as those in the subforms:

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In its AfterUpdate event procedure put the following code:

Dim strFilter As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strFilter = "[CompanyName] IN" & _
"(SELECT [CompanyName] " & _
"FROM [Capacities] " & _
"WHERE [PersonnelID] = " & ctrl & ")"
Me.Filter = strFilter
Me.FilterOn = True

Note that, unlike the combo box for finding a company, this filters the
form. This is because more than one company might be associated with the
selected person. You can clear the filter using the built in button on the
toolbar, or you can add a 'Show All' button to the main form with the
following in its Click event procedu

Me.FilterOn = False

One thing you might like to do before you do any of this is check that the
tables, now that you've got all the data in, do give the correct results when
joined. You can do this with a query which will list all companies, with
their directors, officers, shareholders. You don't have to save the query if
you don't wish to, just create it in SQL view and then switch to datasheet
view to see the results:

SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName]
FROM [Companies], [Capacities], [Personnel]
WHERE [Companies].[CompanyName] = [Capacities].[CompanyName]
AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID]
ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname];

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but
I didnt see where I had to put "ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

From the instructions you gave, I think I missed an important step here.


  #28  
Old November 14th, 2008, 05:50 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Oh man am I an idiot....I finally figured out what you have been saying...I
was reading it all wrong...I now realize that if I click on the Capacity
field I can "Change To" a combo box! Sorry about that...it helps when you
don't have an idiot reading instructions! LOL
  #29  
Old November 14th, 2008, 06:45 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Ken

Well I thought I had it but obviously I don't. In my subform I made the
Capacity field into a combo box and the label as the text box but it makes it
unbound. I tried doing it with the add fields button but it still comes up
as an unbound text box. When I go to the properties page and select control
source to make it a bound text box the only options are the Capacity,
PersonnelID and CompanyName. There isn't an option for me to select a
DefaultValue property to Director
  #30  
Old November 15th, 2008, 07:36 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Ken

I was wondering if I could send you what I have for review. I am not
getting the results that I should be with the subforms. It is showing 3
records for Director and 3462 for Officers and 3600 for shareholders...I am
pretty sure that one company doesn't have that many shareholders. LOL. You
would really help me out if you could review this and advise me of things
that I have done wrong.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 OfficeFrustration.
The comments are property of their posters.