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
  #11  
Old November 9th, 2008, 11:36 PM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Okay so I do see that the qryCompaniesTotal and qryDirectorsTotal are
populated and the tblCompanies and tblDirectors are empty. When I do the
form it isn't populating the information. If I want A.G.C. company and
select it, the accompanying Director doesn't come up with it like I would
like it to. Each time I do a search by company, director, shareholder or
officer I need it to populate the rest of the information associated with
that id. I did notice that when I ran the query the first time, there was no
id field so I re ran the query and it populated the fields in each but it
still isn't functioning right...I obviously missed a step or have messed up
the relationship or something because the queries did what you said they
would do. At this point, I am not sure what I have done wrong or need to do
to get this thing to work. Thank you for your patience Wayne, I must be a
real piece of work for you!
  #12  
Old November 10th, 2008, 01:28 AM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default Multiple Records

There are alternative models you can use for this. By having separate tables
for each Wayne's in essence treats directors, officers and shareholders as
sub-types of an overall 'people' type. That's a good model if you need to
record different attributes of each sub-type as you can add the relevant
columns to each table for this. You should really have another table
Personnel say which includes everybody and has columns such as FirstName and
LastName for those attributes common to all. The three sub-types tables will
then be related to this and include columns only for the attributes specific
to each sub-type. This way you eliminate the redundancy of storing the same
people's names more than once, which is not only uneconomical but risky as it
allows for inconsistencies (I once found three versions of my name in one
database as author of technical papers in my own area of work; as far as the
database is concerned I'm three separate people!)

Another way of modelling it would be to have one Personnel table for
everybody and then a table Capacities related to this which includes the
capacity in which each person is connected to the company, i.e. Director,
Officer or Shareholder. With this model Director, Officer or Shareholder are
being treated as attribute values rather than as sub-types of a people entity
type. This model works well if you only want to record attributes such as
names which are common to everybody. A complicating factor, however, is that
a person could be associated with more than one company, so there is a
many-to-many relationship between Companies and Personnel, which in each case
could involve the person being involved with the different companies in the
same or different capacities, e.g. they might be shareholders of both, or a
shareholder of one and a director of another. A many-to-many relationship is
modelled by a third table, the Capacities table in this case, so with this
model the tables would be:

Companies
….Company Name (Text - Primary Key)
….Incorporation # (Text or Number as appropriate)
….Date of Incorporation (Date/Time)
….SD&C File # (Text or Number as appropriate)

(I've assumed that company names are unique)

Personnel
….PersonnelID (Autonumber - Primary Key)
….Last Name (Text)
….First Name (Text)

Capacities
….PersonnelID (Long Integer Number)
….Company Name (Text)
….Capacity (Text)

The primary key of this last table is a composite one of all three columns.

Lets assume that your table created from the imported Excel data is called
ExcelTable. You can fill the Companies table with an 'append' query:

INSERT INTO Companies ([Company Name],[Incorporation #], [Date of
Incorporation], [SD&C File #])
SELECT DISTINCT [Company Name],[Incorporation #], [Date of Incorporation],
[SD&C File #]
FROM [ExcelTable];

Before filling the Personnel Table you need to pull all the names from the
separate columns in the ExcelTable together into the same Last Name and First
Name columns with a UNION query:

SELECT [Directors Last Name] AS [Last Name], [Directors First Name] AS
[First Name]
FROM [ExcelTable ]
UNION
SELECT [Officer Last Name], [Officer First Name]
FROM [ExcelTable ]
UNION
SELECT [Shareholders Last Name], [Shareholders First Name]
FROM [ExcelTable ];

Save this as qryAllPersonnel say. A UNION query suppresses duplicate rows
so the result will have only one of each, this does assume that no two people
have the same first and last names of course. You can now append rows to
Personnel with:

INSERT INTO [Personnel] ([Last Name], [First Name])
SELECT [Last Name], [First Name]
FROM [qryAllPersonnel];

The above could be done in one step, but you might find it easier to create
the UNION query first and then a separate 'append' query.

The PersonnelID column will as an autonumber be given arbitrary unique
values automatically. This means that different people with the same names
can be distinguished – never use personal names as keys.

The remaining task now is to fill the Capacities table. This requires the
ExcelTable being joined to the Personnel table in an 'append' query, and the
easiest way is to do it in three stages, once for directors once for officers
and once for shareholders, so for directors:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Director"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Directors Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Directors First Name];

For shareholders:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Shareholder"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Shareholders Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Shareholders First Name];

and for officers:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Officer"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Officer Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Officer First Name];

As far as a form is concerned I'd forget about list boxes. Instead create a
companies form in single form view with separate directors, officers and
shareholders subforms within it, basing each subform on a separate query; for
directors:

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

For shareholders:

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

And for officers:

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

Link each subform to the parent form on Company name by setting this as the
LinkMasterFields and LinkChildFields properties of the subform control in
each case. Each subform would be in continuous form view and have 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. This means
that you can add a person in the relevant capacity as a new record in each
subform if necessary provided that they already exist in the personnel table.
If the person is not yet in the Personnel, table you'll first need to add
them to that of course, for which you can have a simple personnel form based
on the personnel table. There is away you can enter a new person directly
via the combo box on the subform, but lets not complicate things too much at
this stage. We can always come back to that.

When you navigate to a company in the parent form each subform will show the
directors, officers and shareholders of the company.

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 [Company Name] FROM [Companies] ORDER BY [Company Name];

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 "[Company Name] = """ & 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], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];

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 = "[Company Name] IN" & _
"(SELECT [Company Name] " & _
"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

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

I have a corporate database that has multiple directors, officers and
shareholders and would like to list all of the directors in one list box in
the form...so if I pull up ABC Company and click on the director name it
lists all of the directors. 2ndly when I search sometimes that person could
be in all three categories...is there any way that I can do a search and have
it pull up everything with that name rather than clicking on next each and
every time. Sorry, I am still new with all of this


  #13  
Old November 10th, 2008, 02:17 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Ken
When I run the append query I am now getting an error message that says it
can't append all the records in the append query. "Microsoft Office Access
set 0 field(s) to Null due to a type conversion failure, and it didn't add
828 record(s) to the table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule violations." It populates
the CompaniesAppend tab but not the Companies Table as it did when I
attempted Wayne's way but I didn't get the message error I am getting now. I
haven't attempted the other steps that you have so kindly typed out for me
due to the message I received when attempting to do the append query. The
fields that are in the CompaniesAppend are the CompanyName,
IncorporationNumber, DateOfIncorporation, and SDCFileNumber which is showing
me 828 records in it. Suggestions before I attempt your next step?
  #14  
Old November 10th, 2008, 06:56 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Ken and/or Wayne

I have succeeded in making the queries and populated all the table the way
they should be...well all but the Officers one. I reattempted the format
that you suggested Ken and ran the queries and such and the DirectorsAppend
and ShareholdersAppend both worked and are in my Capacities table but when I
try and do the OfficersAppend I get this message "Microsoft Office Access
can't represent the join expression [Personnel].[LastName] =
[TblMyExcelStuff].[OfficersLastName] in Design View....*One or more fields
may have been deleted or renamed, *The name of one or more fields or tables
specified in the join expression may be misspelled, * The join may use an
operator that isn't supported in Design view such as or " But I have
checked the fields and didn't see anything misspelled. I copied the code
into my OfficersAppend query and saved it and then went into Design Tab and
clicked Run...thus the error.
  #15  
Old November 10th, 2008, 07:28 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

I then proceeded to correct the spelling after figuring out it was just
missing an "s" in Officer...ughhh and was so happy and went to run it and
then it came back with the error I used to get which was MSA set 0 field(s)
to Null due to a type conversion failure, and it didn't add 3489 record(s) to
the table due to key violations, 0 record(s) due to lock violations and 0
record(s) due to validation rule violations....OH SO CLOSE! My Capacities
Table has the PersonnelId, CompanyName, and Capacities(Shareholder and
Director) all populated and is coming together nicely..almost there thanks to
both of you. So if you have any ideas as to why I am now getting this error
and why it won't allow me to run it and append it to the Capacities Table I
would be very appreciative...so ignore my previous message! LOL
  #16  
Old November 10th, 2008, 07:39 AM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Multiple Records

Hi

The form is not a problem. You can get this to work. In an database the
main thing to get right are the tables. If they are working (with
relationships etc) then the rest will come.

I see another small problem though. Please don't take this in the wrong way
it really isn't meant to be rude. You are createing an application that you
don't understand. You will get it to work v soon but what then. You really
should not only make an application that works but only that you understand.
It "will" need working on later to fix a problem, to add other functions, to
alter the UI, etc, etc and if you (as the creator) do not understand the very
basic way in which it works there will be a problem. Basic by the way in
this case the basics of the database the tables, queries, forms, etc.





--
Wayne
Manchester, England.



"pupkiss1965" wrote:

Okay so I do see that the qryCompaniesTotal and qryDirectorsTotal are
populated and the tblCompanies and tblDirectors are empty. When I do the
form it isn't populating the information. If I want A.G.C. company and
select it, the accompanying Director doesn't come up with it like I would
like it to. Each time I do a search by company, director, shareholder or
officer I need it to populate the rest of the information associated with
that id. I did notice that when I ran the query the first time, there was no
id field so I re ran the query and it populated the fields in each but it
still isn't functioning right...I obviously missed a step or have messed up
the relationship or something because the queries did what you said they
would do. At this point, I am not sure what I have done wrong or need to do
to get this thing to work. Thank you for your patience Wayne, I must be a
real piece of work for you!

  #17  
Old November 10th, 2008, 07:43 AM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Multiple Records

I think what you need to do here is to look at the excel sheet and see what
formts you have and make sure they are the same as the tables you are
appending to.
Number to number
text to text
etc

You can always run an update query (on the whole imported excel file) to
alter the formats of the cells/fields as soon as you bring them into access
but this will be another task. Access can change formats on most things but
it may be an idea this time to just tie up the excel formats with the table
formats as this will remove the need to alter the data when you bring it in.




--
Wayne
Manchester, England.



"pupkiss1965" wrote:

I then proceeded to correct the spelling after figuring out it was just
missing an "s" in Officer...ughhh and was so happy and went to run it and
then it came back with the error I used to get which was MSA set 0 field(s)
to Null due to a type conversion failure, and it didn't add 3489 record(s) to
the table due to key violations, 0 record(s) due to lock violations and 0
record(s) due to validation rule violations....OH SO CLOSE! My Capacities
Table has the PersonnelId, CompanyName, and Capacities(Shareholder and
Director) all populated and is coming together nicely..almost there thanks to
both of you. So if you have any ideas as to why I am now getting this error
and why it won't allow me to run it and append it to the Capacities Table I
would be very appreciative...so ignore my previous message! LOL

  #18  
Old November 10th, 2008, 07:56 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

I reviewed the excel spreadsheet and the format is the same as the other
shareholders or directors columns. In the datasheet view of the
OfficersAppend query it shows the PersonnelID, Company Name and the Expr1
being Officer so I can see that it is taking the information that it is
supposed to but when I go over to Design View and click on Run...that is the
message it gives me...and doesn't append the 3489 records that it has in the
datasheet view due to that error code...sorry, I really don't know what that
code means.
  #19  
Old November 10th, 2008, 06:58 PM posted to microsoft.public.access.gettingstarted
Ken Sheridan
external usenet poster
 
Posts: 3,433
Default Multiple Records

As you'll have seen the 'append' queries to insert the rows into the
Capacities table are each just a slight variation of the same basic pattern,
so there's no reason in principle why the one for officers should behave any
differently. Make sure that you have the names of the officers first and
last name columns from the TblMyExcelStuff table exactly right in the append
query.

Ken Sheridan
Stafford, England

"pupkiss1965" wrote:

Ken and/or Wayne

I have succeeded in making the queries and populated all the table the way
they should be...well all but the Officers one. I reattempted the format
that you suggested Ken and ran the queries and such and the DirectorsAppend
and ShareholdersAppend both worked and are in my Capacities table but when I
try and do the OfficersAppend I get this message "Microsoft Office Access
can't represent the join expression [Personnel].[LastName] =
[TblMyExcelStuff].[OfficersLastName] in Design View....*One or more fields
may have been deleted or renamed, *The name of one or more fields or tables
specified in the join expression may be misspelled, * The join may use an
operator that isn't supported in Design view such as or " But I have
checked the fields and didn't see anything misspelled. I copied the code
into my OfficersAppend query and saved it and then went into Design Tab and
clicked Run...thus the error.


  #20  
Old November 11th, 2008, 09:06 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

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?
 




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 06:15 PM.


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