splitting of database

[narayananh76]

i am creating a payroll package. How do i give the customer the option of
using five different companies in the package??
For eg i am use the drop down box and five companies are there. Suppose i
click on one company i should be able to access all the tables of that
particular companies and not other companies.Please help i have beens struck
on this point for a very long time.

Another question is how do we print micr cheques in access??

Regards
Narayanan

 

[Jeff Boyce]

Since we can't see your tables, it will be hard to offer specific
suggestions.

In general, though, it would not usually be necessary to have separate sets
of tables for each company. That might be how you would do it using Excel,
but Access is a relational database. You won't get full use of Access'
relationally-oriented features and functions if you feed it 'sheet data.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[narayananh76 via AccessMonster.com]

Thank you Jeff for your reply. I know access is a relational database.But how
ever is it possible to do as i say. Assume there are three companies A,B, and
C and under A there is name age and address, under B u have the same name age
and address and similarly for C. Now using a drop down box i would like to
give the customer the option that, suppose were he to click for Company name
A ,then would it be possible for him to see only the tables of A.

Since we can't see your tables, it will be hard to offer specific
suggestions.

In general, though, it would not usually be necessary to have separate sets
of tables for each company. That might be how you would do it using Excel,
but Access is a relational database. You won't get full use of Access'
relationally-oriented features and functions if you feed it 'sheet data.

i am creating a payroll package. How do i give the customer the option of
using five different companies in the package??

[quoted text clipped - 7 lines]

Regards
Narayanan

 

[John W. Vinson]

Thank you Jeff for your reply. I know access is a relational database.But how
ever is it possible to do as i say. Assume there are three companies A,B, and
C and under A there is name age and address, under B u have the same name age
and address and similarly for C. Now using a drop down box i would like to
give the customer the option that, suppose were he to click for Company name
A ,then would it be possible for him to see only the tables of A.

Include the CompanyID field in the personnel table; and display to the user -
not the Table - but only a Query selecting the *records* (not the tables) for
company A.

John W. Vinson [MVP]

 

[narayananh76 via AccessMonster.com]

Thank you John for your reply.However what i need is this Imagine there is a
form with a combo box.In the combo box there are company A, B, C. Now suppose
my customer was to click on any of these companies he would be directed to
the tables of the particular company. For eg if my customer was to click on
company A in the combo box.He would be directed to the tables of the
particular company say Employee, Salary ,Permanent employees Temproary
employees etc. Can you suggest the coding that i would have to give in the
form which would make the connectivity to the tables of the particular
company.

Thanks

 

[Rick Brandt]

Thank you John for your reply.However what i need is this Imagine
there is a form with a combo box.In the combo box there are company
A, B, C. Now suppose my customer was to click on any of these
companies he would be directed to the tables of the particular
company. For eg if my customer was to click on company A in the combo
box.He would be directed to the tables of the particular company say
Employee, Salary ,Permanent employees Temproary employees etc. Can
you suggest the coding that i would have to give in the form which
would make the connectivity to the tables of the particular company.

Don't use separate tables per company! That is what John has been trying to
tell you. Use ONE set of tables each having a field that identifies the
company. Then you just use queries that all filter the company on the choice
made in the ComboBox.

Any solution that actually switched amongst different sets of actual tables
would be a mess to manage and would leave open the possibility of something
going wrong that would really mess up your data.

 

[narayananh76 via AccessMonster.com]

Thanks a load Rick, John and Jeff. i fully well understand what you are
trying to say.As i am not that advanced in Access could some one suggest any
tutorial sites that shows me the connection between queries and combo boxes
and list boxes.

Thank you John for your reply.However what i need is this Imagine
there is a form with a combo box.In the combo box there are company

[quoted text clipped - 5 lines]

you suggest the coding that i would have to give in the form which
would make the connectivity to the tables of the particular company.

Don't use separate tables per company! That is what John has been trying to
tell you. Use ONE set of tables each having a field that identifies the
company. Then you just use queries that all filter the company on the choice
made in the ComboBox.

Any solution that actually switched amongst different sets of actual tables
would be a mess to manage and would leave open the possibility of something
going wrong that would really mess up your data.

 

[Douglas J. Steele]

All you need to do is put a reference to the control on the form as the
criteria for the query.

In other words, you'd put

Forms!NameOfForm!NameOfComboBox

as the criteria for the Company code in your query. (replace NameOfForm and
NameOfComboBox with the appropriate names)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks a load Rick, John and Jeff. i fully well understand what you are
trying to say.As i am not that advanced in Access could some one suggest
any
tutorial sites that shows me the connection between queries and combo
boxes
and list boxes.

Thank you John for your reply.However what i need is this Imagine
there is a form with a combo box.In the combo box there are company

[quoted text clipped - 5 lines]

you suggest the coding that i would have to give in the form which
would make the connectivity to the tables of the particular company.

Don't use separate tables per company! That is what John has been trying
to
tell you. Use ONE set of tables each having a field that identifies the
company. Then you just use queries that all filter the company on the
choice
made in the ComboBox.

Any solution that actually switched amongst different sets of actual
tables
would be a mess to manage and would leave open the possibility of
something
going wrong that would really mess up your data.

 

[narayananh76 via AccessMonster.com]

Thanks for the info Douglas. I am a bit raw in access so need to be told
exactly whats to be done. But anyways sorry for being so dumb. I will work on
it and then get back with my doubts.

All you need to do is put a reference to the control on the form as the
criteria for the query.

In other words, you'd put

Forms!NameOfForm!NameOfComboBox

as the criteria for the Company code in your query. (replace NameOfForm and
NameOfComboBox with the appropriate names)

Thanks a load Rick, John and Jeff. i fully well understand what you are
trying to say.As i am not that advanced in Access could some one suggest

[quoted text clipped - 21 lines]

 

[John W. Vinson]

Thanks for the info Douglas. I am a bit raw in access so need to be told
exactly whats to be done. But anyways sorry for being so dumb. I will work on
it and then get back with my doubts.

Let's say you have a table named Employees with fields:

EmployeeID <Autonumber or manually assigned employee number, primary key>
LastName
FirstName
<other biographical data>
CompanyID

and another table Companies:

CompanyID <autonumber or CUSIP or other unique ID>
CompanyName
<other info about the company>


If you have a form, named frmMyForm, with an unbound combo box named
cboCompanies based on the Companies table, you could create a query by adding
Employees to the query grid. Select all the fields. On the Criteria line under
CompanyID type

=[Forms]![frmMyForm]![cboCompanies]

This will create a Query which will display only those records for that
selected company.

As you get further into this, you can implement database security so that an
employee of company A can only view information about company A, but let's get
the basics down first.

John W. Vinson [MVP]

 

[narayananh76 via AccessMonster.com]

Heyy guys sorry for the delay in replying to your message. As per John Vinsom
i created two tables Table No 1 contains Employee ID Primary key and auto
number, First Name , Last name and Company ID.Table No2 contains Company ID
Auto Number , and Company Name. Again as per John i created the query with
Table No 1 as its base, with the criteria assigned to Company id as =[Forms]!
[frmMyForm]![cboCompanies]. I face the following problems

a) I am working on MS Access 2003 and i get the wizard for the combo box but
i am not able to get the final output, ie when i go to the combo box, the
box does not reflect the companies name. So i created a list box with the
name CboCompany which gives me the final output and captures the data of the
companies name.

b) Secondly when i give the criteria =[Forms]![frmMyForm]![cboCompanies] to
the company id , i have to give the id of the company in order to get me
details of the particular company.

What i want
a) is to give the name of the company and details to appear abt the Name
address and other details abt the employees of the particular company.
b) To click on the name of the company in the list box and to get me an
details abt table no1 or employee tables as Vinsom put it.

There is another way i could go abt it and thats to add in the name of the
company in the table no1 or employee table and then create a criteria in the
query under name of the company... Plss help.....

Thanks for the info Douglas. I am a bit raw in access so need to be told
exactly whats to be done. But anyways sorry for being so dumb. I will work on
it and then get back with my doubts.

Let's say you have a table named Employees with fields:

EmployeeID <Autonumber or manually assigned employee number, primary key>
LastName
FirstName
<other biographical data>
CompanyID

and another table Companies:

CompanyID <autonumber or CUSIP or other unique ID>
CompanyName
<other info about the company>

If you have a form, named frmMyForm, with an unbound combo box named
cboCompanies based on the Companies table, you could create a query by adding
Employees to the query grid. Select all the fields. On the Criteria line under
CompanyID type

=[Forms]![frmMyForm]![cboCompanies]

This will create a Query which will display only those records for that
selected company.

As you get further into this, you can implement database security so that an
employee of company A can only view information about company A, but let's get
the basics down first.

John W. Vinson [MVP]