third table relationship and query on form

[Guest]

dear comrades,
i have three tables making for an trade union accounts viz
BRANCHES EMPLOYEES MEMBERSHIP DEVELOPMENTFUND EXPENCES
branh id(pk) names names(loopup) names(lookup)
branchid(lookup)
employeeid(pk) memberid(pk) devlopmentid(pk) date
branchid(fk) amount memberid(fk)
item
finacial year amount
amount
financial
year finacial year
(fk) branch id(lookup) branchid(lookup)
this is the stucture of my database my problom is
(1) names lookup in MEMBERSHIP AND DEVELOPMENTFUND TABLE, shouldbe According
to the branchid selected, only the names lookup column should sort the
nemesaccording to branchid, how to use an query accordingly to sort
automaticaly according to branchid lookup and how to get sum of
membershiamount and sum of development amount and minus expences. kindly tell
me any wrong in the setted relationship.specially between Expences and other
table(s).
(2)I want to place one text box over the form header and make it an look up
box
for eg,Finacialyear and imade
employees!financialyear!data!controlsource=textbox.text!on formheader.but it
only been heighlighted in field textbox but not getting saved how do that
autosaving in the field.
(3) on Developmentfund table i got forighnkey field memberid. is which is
absolutly necessery ? iwant setup one query that as soon as i selected name
in the names look up field i want the memberid to automatically exibhit and
save in the memberid field.so as i setup an selectquery SELECT memberid FROM
membership!names. in the CRITERIA i set up ,=form!developmentfund!names but
nor its working
neighther its getting saved.
kindly help me
yours balu.

 

[Jeff Boyce]

The email reader I use reorganized your carefully laid-out description of
your tables.

Consider reposting your description in a vertical alignment, something like:

tblPerson
PersonID (PK)
FirstName
LastName
...

By the way, if your "(lookup)" means you've used the lookup wizard inside
the table definition, you will find that Access stores one value, but
displays something else. A review of the tablesdbdesign newsgroup will
reveal a STRONG consensus against using lookup fields in table definition.

Lookup tables, fine, but used via combo boxes in forms...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

dear "jeff"
i m extreemly sorry for the misalaighnment of typing the tables.
TABLE BRANCHES
branch id (pk)
branch name -text
TABLE EMPLOYEES
Employee name - text
employee id -number (pk)
branch id -(FK) --NOTE imade it look up wizard for easy data entry
TABLE BOOKS .. NOTE, BOOKS alloted for collection of funds for each branch
Book id (pk)..number
branch id (fk)..number
financial year..date/time ..yyyy-yyyy
TABLE MEM ...NOTE.membership collection table
employee name -lookup to employee name !emploees
branch id -"NOTE.here as soon as i selected employee name the query shall
auto fill branch id or good mistake proofing method while data entry "(fk)
memslip number -number.(PK) Membrer ship slip number
book number
amount .curency
TABLE DEV. NOTE.TABLE FOR DEVELOPMENT FUND COLLECTION
Employe name -lookup to employee name !employees
dev slip number (pk)
branch id (fk) as well shall auto fill through query
amount -currency
book number
amount
TABLE EXPENCES
BRANCH ID -NUMBER lookup to branchid ! branches
items -memo
date
expences-currency
auto number (pk)
dear jeff this is my primary desighn my probloms (1) many tables are
dependant on branches table and have one to many relation ships is it works
out i m getting absolutly un-understandable SELECTQUERY result for
branch id , employee name ,mem slip number,dev slip number
(2) query. branch id ,sum(mem amount),sum(dev amount)
(3) i would like to have an form on its header i want to have an combo
boxes for
branch id and according to selected branch id only the book numbers and
employeenames should be sorted out in the lookup columns of all concerned how
to doit please kindly advise please
ur balu

 

[Jeff Boyce]

see comments in-line below...

dear "jeff"
i m extreemly sorry for the misalaighnment of typing the tables.
TABLE BRANCHES
branch id (pk)
branch name -text
TABLE EMPLOYEES
Employee name - text
employee id -number (pk)
branch id -(FK) --NOTE imade it look up wizard for easy data entry

Two potential issues: 1) using the lookup wizard means your table stores
one value (the ID) while displaying a different value (the looked-up value).
This can be confusing, and (incorrectly, in my opinion) encourages users to
work directly in the tables. 2) If you are storing a [BranchID] in your
employee table, you have determined that one employee can serve at ONLY ONE
branch. If this doesn't match your real-world situation, you need another
table that shows any/all valid Employee-Branch combinations.

TABLE BOOKS .. NOTE, BOOKS alloted for collection of funds for each branch
Book id (pk)..number
branch id (fk)..number
financial year..date/time ..yyyy-yyyy
TABLE MEM ...NOTE.membership collection table
employee name -lookup to employee name !emploees
branch id -"NOTE.here as soon as i selected employee name the query shall
auto fill branch id or good mistake proofing method while data entry "(fk)
memslip number -number.(PK) Membrer ship slip number
book number
amount .curency

I don't understand what EmployeeID has to do with Book. I suspect you need
a Book table that ONLY lists books, then another table that shows which book
is associated with which employee. What does "branch" have to do with this?

Your [FinancialYear] field appears to contain two "facts" (yyyy-yyyy). One
field, one fact...

You mention "... the query shall auto fill ..." What query? Is this being
done in a form?

TABLE DEV. NOTE.TABLE FOR DEVELOPMENT FUND COLLECTION
Employe name -lookup to employee name !employees
dev slip number (pk)
branch id (fk) as well shall auto fill through query
amount -currency
book number
amount

How is this related to the previous tables?

TABLE EXPENCES
BRANCH ID -NUMBER lookup to branchid ! branches
items -memo
date
expences-currency
auto number (pk)
dear jeff this is my primary desighn my probloms (1) many tables are
dependant on branches table and have one to many relation ships is it works
out i m getting absolutly un-understandable SELECTQUERY result for
branch id , employee name ,mem slip number,dev slip number
(2) query. branch id ,sum(mem amount),sum(dev amount)
(3) i would like to have an form on its header i want to have an combo
boxes for
branch id and according to selected branch id only the book numbers and
employeenames should be sorted out in the lookup columns of all concerned how
to doit please kindly advise please
ur balu

I'll suggest that you may benefit from looking into "normalization". Your
tables appear to have combined several thoughts together, rather than
keeping them separated.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/