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. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Multiple Records
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 |
#2
|
|||
|
|||
Multiple Records
Hi
If you have 2 combo boxes on your form you can write some code do this, where you select an item from the 1st and - AfterUpdate - you filter the items available in the 2nd. It is called casscadeing combos If you are not familiar with write code you can also do it like this (using the wizards) Open you form in design view Add a combo box. In the wizard box that opens click cancel Do this again so you have 2 new combos Right click the first combo and open the properties box In the data column select the Row Souce Tyep row = Table/Query In the Row Source row click the build option (...) This will open a query builder Add the ID Add the company Name Do the same for the 2nd combo But in this combo you must add the table with the companies "and" the directors Add the ID from the directors table Add the directors name from the directors table Save "ALSO" the ID from the companies table In this column click the Criteria row and select build select forms Select the 1st combo you have just made Save Go back to the form and give it a try. You may need to later the row widths to allow you hide the ID's in the combos and show the names (try this by setting the width to 0; and then changing it to see what you like the look of) Good luck with your application -- Wayne Manchester, 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 |
#3
|
|||
|
|||
Multiple Records
I did the steps that you suggested and I it is not giving me the results that
I need. What I want to see is when I do a search for example for ABC Company I would like to see all of the directors, shareholders and officers associated with that company. I think that the table I am using may be interferring. I imported an excel sheet for my table. This is the data that our old database exported to excel. Company Name Incorporation # Date of Incorporation ABC Company 123456 12/01/1977 SD&C File # Directors Last Name Directors First Name 01234 Doe Greg Doe Greg Officer Last Name Officer First Name Shareholders Last Name Smith David Carter Shareholders First Name Aileen It then repeats and has all the same information on the next line but this time with David Smith as the next director. So basically I may have 5 different entries for the same company because there are 5 different directors/shareholders/officers. One entry for every new name in any one of the 3 columns (director/shareholder/officer). I want to show all associated persons with the one company for each of the categories...keeping in mind that each entry has the same Company name, incorporation #, date, and sdc # and I only want to see that one company whereas with the combo box that I created when I click on it, it shows me all of the companies as if for me to select one in a list..I don't want to select I want it to give me all the information. I hope this makes sense.! and thanks for the help "Wayne-I-M" wrote: Hi If you have 2 combo boxes on your form you can write some code do this, where you select an item from the 1st and - AfterUpdate - you filter the items available in the 2nd. It is called casscadeing combos If you are not familiar with write code you can also do it like this (using the wizards) Open you form in design view Add a combo box. In the wizard box that opens click cancel Do this again so you have 2 new combos Right click the first combo and open the properties box In the data column select the Row Souce Tyep row = Table/Query In the Row Source row click the build option (...) This will open a query builder Add the ID Add the company Name Do the same for the 2nd combo But in this combo you must add the table with the companies "and" the directors Add the ID from the directors table Add the directors name from the directors table Save "ALSO" the ID from the companies table In this column click the Criteria row and select build select forms Select the 1st combo you have just made Save Go back to the form and give it a try. You may need to later the row widths to allow you hide the ID's in the combos and show the names (try this by setting the width to 0; and then changing it to see what you like the look of) Good luck with your application -- Wayne Manchester, 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 |
#4
|
|||
|
|||
Multiple Records
Hi I think the table are where your problems are as well :-) Don't worry you can get round this. There are a number of options. You " could" use the same table as you have and then run some code to select all the directors from each compnay. You "could" just base your forms on a totals query so that each company is shown only once and then run the other forum of your normal tables. etc etc There are lots of way round it But ...... and this is just my opinion so others in this forum may disagree and tell you something else. So bare in mind this is just one of the methods. If it were me (coz I'm a pedant - new english word for today by the way :-) I would try and get everything how it should be before I started doing other "stuff". Its not hard and nce it up and running it will work again and again. So You have imported a table from excel (I think). In your table you have these fields Company Name Incorporation # Date of Incorporation SD&C File Directors Last Name Directors First Name Officer Last Name Officer First Name Shareholders Last Name Hope this right so far. If it were me I would import the file from excel and (in the same module) run an update query to various tables. But in this case can I suggest that you do it the "old fashioned" way. By clicking the mouse a few times :-) You are going to create an update query The first thing to do is to create the tables Note I have taken the spaces out of the names you use and the & simbol 1st 1 Call it tblCompanies Put these fields into your new table CompanyID = autonumber CompanyName = text Incorporation = (not sure what this is so I’ll leave it to you) DateOofIncorporation = date SDCFile = (again not sure what this is so I’ll leave it to you) 2nd table Call it tblDirectors DirectorID = autonumber DirectorsLastName = Text Directors First Name = Text 3rd table Call it tblOfficers OfficerID = Autonumber OfficerLastName = Text Officer First Name = Text Not really sure where the shareholder should go – what is it ? ShareholdersLastName Anyway now you have your tables sorted – I have assume that you are using an import and all the data is going into a table called tblMyExcelStuff Ooops Sorry got to go now (family thing). If you spend some time createing the tables I will do the rest this evening – unless someone else doesn’t jump in with the update queries -- Wayne Manchester, England. "pupkiss1965" wrote: I did the steps that you suggested and I it is not giving me the results that I need. What I want to see is when I do a search for example for ABC Company I would like to see all of the directors, shareholders and officers associated with that company. I think that the table I am using may be interferring. I imported an excel sheet for my table. This is the data that our old database exported to excel. Company Name Incorporation # Date of Incorporation ABC Company 123456 12/01/1977 SD&C File # Directors Last Name Directors First Name 01234 Doe Greg Doe Greg Officer Last Name Officer First Name Shareholders Last Name Smith David Carter Shareholders First Name Aileen It then repeats and has all the same information on the next line but this time with David Smith as the next director. So basically I may have 5 different entries for the same company because there are 5 different directors/shareholders/officers. One entry for every new name in any one of the 3 columns (director/shareholder/officer). I want to show all associated persons with the one company for each of the categories...keeping in mind that each entry has the same Company name, incorporation #, date, and sdc # and I only want to see that one company whereas with the combo box that I created when I click on it, it shows me all of the companies as if for me to select one in a list..I don't want to select I want it to give me all the information. I hope this makes sense.! and thanks for the help "Wayne-I-M" wrote: Hi If you have 2 combo boxes on your form you can write some code do this, where you select an item from the 1st and - AfterUpdate - you filter the items available in the 2nd. It is called casscadeing combos If you are not familiar with write code you can also do it like this (using the wizards) Open you form in design view Add a combo box. In the wizard box that opens click cancel Do this again so you have 2 new combos Right click the first combo and open the properties box In the data column select the Row Souce Tyep row = Table/Query In the Row Source row click the build option (...) This will open a query builder Add the ID Add the company Name Do the same for the 2nd combo But in this combo you must add the table with the companies "and" the directors Add the ID from the directors table Add the directors name from the directors table Save "ALSO" the ID from the companies table In this column click the Criteria row and select build select forms Select the 1st combo you have just made Save Go back to the form and give it a try. You may need to later the row widths to allow you hide the ID's in the combos and show the names (try this by setting the width to 0; and then changing it to see what you like the look of) Good luck with your application -- Wayne Manchester, 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 |
#5
|
|||
|
|||
Multiple Records
Did you create the tables ?
-- Wayne Manchester, England. "pupkiss1965" wrote: I did the steps that you suggested and I it is not giving me the results that I need. What I want to see is when I do a search for example for ABC Company I would like to see all of the directors, shareholders and officers associated with that company. I think that the table I am using may be interferring. I imported an excel sheet for my table. This is the data that our old database exported to excel. Company Name Incorporation # Date of Incorporation ABC Company 123456 12/01/1977 SD&C File # Directors Last Name Directors First Name 01234 Doe Greg Doe Greg Officer Last Name Officer First Name Shareholders Last Name Smith David Carter Shareholders First Name Aileen It then repeats and has all the same information on the next line but this time with David Smith as the next director. So basically I may have 5 different entries for the same company because there are 5 different directors/shareholders/officers. One entry for every new name in any one of the 3 columns (director/shareholder/officer). I want to show all associated persons with the one company for each of the categories...keeping in mind that each entry has the same Company name, incorporation #, date, and sdc # and I only want to see that one company whereas with the combo box that I created when I click on it, it shows me all of the companies as if for me to select one in a list..I don't want to select I want it to give me all the information. I hope this makes sense.! and thanks for the help "Wayne-I-M" wrote: Hi If you have 2 combo boxes on your form you can write some code do this, where you select an item from the 1st and - AfterUpdate - you filter the items available in the 2nd. It is called casscadeing combos If you are not familiar with write code you can also do it like this (using the wizards) Open you form in design view Add a combo box. In the wizard box that opens click cancel Do this again so you have 2 new combos Right click the first combo and open the properties box In the data column select the Row Souce Tyep row = Table/Query In the Row Source row click the build option (...) This will open a query builder Add the ID Add the company Name Do the same for the 2nd combo But in this combo you must add the table with the companies "and" the directors Add the ID from the directors table Add the directors name from the directors table Save "ALSO" the ID from the companies table In this column click the Criteria row and select build select forms Select the 1st combo you have just made Save Go back to the form and give it a try. You may need to later the row widths to allow you hide the ID's in the combos and show the names (try this by setting the width to 0; and then changing it to see what you like the look of) Good luck with your application -- Wayne Manchester, 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 |
#6
|
|||
|
|||
Multiple Records
I have created all the tables as you suggested. I now have a table for each
of the columns that were brought over from excel spreadsheet. SDC is just a file number and shareholders I called it tblShareholders. |
#7
|
|||
|
|||
Multiple Records
Sorry don't have much time - saturday night and all that :-)
Anyway I asusme that you have created the tables ? Open the relationship window and link the companies table with the directors table (using the CompanyID) The best next step would be to use a query with a subquery to give the the diectors of each company the correct company ID (which you need to like the tables - and this will enable your form to work as you want it. Check out Allans Brown's site for the best way to create query/subs But for now we will do it with 4 queries - 2 totals query and 2 append querries They MUST be run in the right order or they will not work (as you can not give a likning field to the directors until each company as a primary field) So create four queries qryCompaniesTotal qryDirectorsTotal qryDirectorsAppends qryCompaniesAppend I assume you have used the field names I gave. If not them give it a try with those names and them change them later (just so you can ee who it works) Open each of the queries in design view and select Veiw - SQL Paste this into each qry and then save an close (without running) qryCompaniesTotal SELECT tblMyExcelStuff.[Company Name], tblMyExcelStuff.Incorporation, tblMyExcelStuff.DateOfIncorperation FROM tblMyExcelStuff GROUP BY tblMyExcelStuff.[Company Name], tblMyExcelStuff.Incorporation, tblMyExcelStuff.DateOfIncorperation; qryDirectorsTotal SELECT tblMyExcelStuff.[Company Name], tblMyExcelStuff.DirectorsLastName, tblMyExcelStuff.DirectorsFirstName, tblCompanies.CompanyID FROM tblCompanies RIGHT JOIN tblMyExcelStuff ON tblCompanies.[Company Name] = tblMyExcelStuff.[Company Name]; qryDirectorsAppend INSERT INTO tblDirectors ( DirectorsLastName, DirectorsFirstName, CompanyID ) SELECT qryDirectorsTotal.DirectorsLastName, qryDirectorsTotal.DirectorsFirstName, qryDirectorsTotal.CompanyID FROM qryDirectorsTotal; qryCompaniesAppend INSERT INTO tblCompanies ( [Company Name], Incorporation, DateOfIncorperation ) SELECT qryCompaniesTotal.[Company Name], qryCompaniesTotal.Incorporation, qryCompaniesTotal.DateOfIncorperation FROM qryCompaniesTotal; OK - if all that is doen. Next open the tables Directors and companies and make sure they are empty - delete all records Next click qryCompaniesAppend Select yes to update Click qryDirectorsAppend SelectYes to update Now your form should work I hope :-) -- Wayne Manchester, England. "pupkiss1965" wrote: I have created all the tables as you suggested. I now have a table for each of the columns that were brought over from excel spreadsheet. SDC is just a file number and shareholders I called it tblShareholders. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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. |
|
Thread Tools | |
Display Modes | |
|
|