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
  #1  
Old November 7th, 2008, 08:14 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default 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  
Old November 7th, 2008, 11:46 AM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old November 7th, 2008, 11:33 PM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default 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  
Old November 8th, 2008, 02:45 PM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old November 8th, 2008, 06:53 PM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old November 8th, 2008, 07:32 PM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default 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  
Old November 8th, 2008, 09:16 PM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default 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  
Old November 9th, 2008, 01:40 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Hi Wayne, I attempted a couple of times to run the queries etc and it
populates the companies but not the directors. I followed the instructions
you gave me but I must be missing something. Is there anyway that I could
send you what I have thus far so you can review and let me know what I have
been doing wrong. I can send it to you via email with the spreadsheet that I
am using. It would be nice to know what it is that I am doing wrong! LOL as
I created all the tables and labelled them what you suggested and then I ran
the 2 queries and 2 subqueries and then when I went to my form it was
blank...I feel like an idiot right now but it may be from fiddling around
with this for sometime now and I am just not seeing my mistake.
  #9  
Old November 9th, 2008, 10:28 AM posted to microsoft.public.access.gettingstarted
pupkiss1965
external usenet poster
 
Posts: 24
Default Multiple Records

Okay, I tried again and what I noticed is that it populated the
qryDirectorsTotal and the qryCompaniesTotal but not the tblDirectors or
tblCompanies. So I know that I have definitely gone wrong here. When I
click on the Create tab and then click on QueryDesign it automatically comes
up with the Show Table window. For the qry's I chose the tblMyExcelStuff and
for the appends I chose the Queries tab and selected the qryDirectorTotal and
the qryCompanyTotal. Was I supposed to choose the tblMyExcelStuff or should
I have not chosen any of them and just pressed close and then went to SQL
view? They didn't automatically populate after clicking the Update
button....I had to click on the Run for it to even at least populate in the
qryCompaniesTotal and qryDirectorsTotal. I am sorry for the questions but
you have been so helpful and you are a very brilliant man for Access...so I
cherish your assistance but I understand if you have had enough of me.
  #10  
Old November 9th, 2008, 02:10 PM posted to microsoft.public.access.gettingstarted
Wayne-I-M
external usenet poster
 
Posts: 3,674
Default Multiple Records

Hi

the 2 totals queries don't "run" as such. You should be able to just open
them.

Open the companies 1st and make sure you companies are showing - you should
see just one of each.

Go to the companies table and delete all the record just to make it simple.

So you should be able to your companies in the companies total query ?
The should be nothing in the new companies table.

Click run on companies append and it should add the companies from your
import table from ecel to the new table and allocated each an ID.

If this is working do the same with the directors.

Let me know if its working.

If not I will create an email account some where - can you zip the file as
most e mail accounts will allow access db's unless zipped (I think)



--
Wayne
Manchester, England.



"pupkiss1965" wrote:

Okay, I tried again and what I noticed is that it populated the
qryDirectorsTotal and the qryCompaniesTotal but not the tblDirectors or
tblCompanies. So I know that I have definitely gone wrong here. When I
click on the Create tab and then click on QueryDesign it automatically comes
up with the Show Table window. For the qry's I chose the tblMyExcelStuff and
for the appends I chose the Queries tab and selected the qryDirectorTotal and
the qryCompanyTotal. Was I supposed to choose the tblMyExcelStuff or should
I have not chosen any of them and just pressed close and then went to SQL
view? They didn't automatically populate after clicking the Update
button....I had to click on the Run for it to even at least populate in the
qryCompaniesTotal and qryDirectorsTotal. I am sorry for the questions but
you have been so helpful and you are a very brilliant man for Access...so I
cherish your assistance but I understand if you have had enough of me.

 




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 12:53 AM.


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