one field require more than on text box

[rudwan]

i have file mange telephone bills system
main table has coulmn:
billID-Telph NO-Amount-personnel call-emp code
while i am posting each bill trough a form , if any
personnel call i should post it in personnel call whith
relate emplyee id,
but suppose i have for one bill , personnel call related
to more than one employee , who i can post a details for
personnel calls with employee id ?

 

[Chadlon]

Hi Rudwan,

Your two postings - this one and the 7 minutes earlier one re the subform -
are in conflict.
And at the same time go some way to solving your problem.

If the phone bill contains a list of calls and these calls are what is being
entered into the subform, then your "personal use" issue can be resolved by
removing the logical link between the Bill and the employee and instead
having a link between a call and an employee. A suggested redraft might be

Bills table:
BillId PK (primary key) - autonumber
BillRefNo (invoice no or whatever)
BillDate Date/Time
TelNo Phone number or Account number
InvValue Amount of Bill (may need other fields for Tax etc)
Checked Date or Boolean to indicate bill passed as OK
Paid Date or Boolean to indicate bill paid
etc

Employees table:
EmpId PK - autonumber
EmpCode Payroll ref or Initials or whatever - ideally unique
Usual Extn Office extension they usually work from??
Name, address etc etc

Calls table
CallId PK - autonumber
BillId FK (foreign key) into Bills table
Only non-null if billable (?Outgoing external ?)
call ??
Call DateTime
Call Method Incoming, Outgoing, Transfer, Hold etc etc
Call Duration
Billed amount
Called No
Calling Extn
Personal Call Boolean
EmpId FK into Employees table
Only non-null if PersonalCall is True ??
etc

So the subform into which you enter the call details can have a combo-box
linking, where necessary, a call to an employee.
Kind of begs the questions where this detail comes from, who gets to enter
it and when. Is any of the info coming from an internal telephone system?
One gets the feeling that the 'personal/not personal' use thing would at
least need another master list of known customer numbers, supplier numbers
etc so that the bulk of the calls could be semi-automatically filtered out -
and even that's an oversimplification if an employee's partner happens to
work for a customer/supplier and so on.

With regard to your other question about totalling items in the subform ...
this has been covered many times in the forms and formscoding groups
(microsoft.public.access.forms via Google Groups)

It is quite legitimate to maintain a running sum of the call items as they
are entered, at least for comparison purposes with the amount billed. It is
not however usually necessary or advisable to store that total in the
database, since it is a derived quantity. It may be expedient to include a
flag in the bills table record to indicate that the amount is in dispute if
the calculated amount is not equal to the billed amount.

CD

 

[rudwan]

Sorry mr Chadlon because i have a problem in
windows , active x , i faile to post replay through
microsft web site

I will not leave you , because I posted a description of
my database to many sites , but you are the one who have a
complete idea of its flowshart ,

Any how , I managed the payment of telephone bills in my
company , televom company issued telephone bills by
accouny number , where one account number may be has one
or more telephone number , so they issued the bills for
the account by total amount , attached with a details of
each telephone bill amount and calls , but payment will be
by the the summary bill , while I need the details for
controlling purposes ,

So I had mainly 4 tables ,

Account table :

AccId PK (primary key) – autonumber
AccNo number

Telephone table :
TelepID PK (primary key) – autonumber
TelephNo number
AccNo number fk(account table . accno )

Bill table :
billId PK (primary key) – autonumber
BillDate Date/Time
AccNo Account number
BillValue number
Checked Date or Boolean to indicate bill passed
as OK
Paid Date or Boolean to indicate bill paid
BillDetails :
DetailsID : PK (primary key) – autonumber
BillID FK ( BILL TABLE)
BillDate Date/Time
TelNo Phone number
InvValue Amount of Bill

You may say : no need for table no 3 ( bill table ) ,
because this can be derived by a query ,
I will answer , yes , you are right , but who I can post
(checked / paid )columns , group by query will not allowed
to edit .
So , I designed a form for ( bill table ) with sub_form
for ( bill details ), and automatic sum will be applied to
(bill table.billvalue)
May be that is not the correct way , but I want your
advice for the best way ,
Regarding personnel calls , I am not concerning to post by
call , just I want to put a lump_sum of international
calls as a private , reference with employee id , if I
will use another table for the calls , it will be a form (
for bill table ) with sub_form ( bill details ) with
sub_form ( call details ) ?

 

[Chadlon]

OK, so the phone bill arrives with summary and detail billing for a phone
account which may be one or more telephone numbers.

And - as I understand it - you are happy with the methods for getting the
various details into your (?proposed?) table structure. You have one or more
phone accounts, each of which has one or more telephone numbers associated
with it. The bills are received (and paid) relative to the account, but also
contain summary and detail information by phone number, which you use for
management purposes. From some other source, you have information regarding
the personal use of phone by employees and this you want to relate to ...
presumably the bill which covers the usage? This last point is not entirely
clear, and is determined by how/from where you establish the personal use
data. But for the purpose of moving on, let's say it can be related to the
bill.

The table setup then could look like:
Account, Bill, Telephone
ComponentOfBillForSpecificPhone aka BillComponent
Employee
PersonalUse

The Account table has a one-to-many relationship with Bill
The Account table has a one-to-many relationship with Telephone
The Bill table has a one-to-many relationship with BillComponent
The Telephone table has a one-to-many relationship with BillComponent
The Bill table has a one-to-many relationship with PersonalUse
The Employee table has a one-to-many relationship with PersonalUse

Some of the detail of the table fields you have not got quite right - I mean
in spirit, not detail ... the detail is up to you. But the method of linking
one table to another, and what info needs to be where, has got a bit
scrambled, so I'll recap.

Account table:
AcctId PK (primary key) - autonumber
AcctRefNo (external Account Number) - text


Telephone table:
TelephId PK - autonumber
AcctId FK (foreign key) into Account - numeric / long
TelephNo (actual external Phone Number) - text


Bill table:
BillId PK - autonumber
AcctId FK into Account - numeric / long
BillRefNo (invoice no or whatever) - text
BillDate Date/Time
BillValue Amount of Bill - currency
(may need other fields for Tax etc)
BillChecked Date or Boolean to indicate bill passed as OK
BillPaid Date or Boolean to indicate bill paid
etc


BillComponent table:
BillCompId PK - autonumber
BillId FK into Bill - numeric / long
TelephId FK into Telephone - numeric / long
CompValue Analysis value - currency
There is a case to make that the sum of CompValue for all BillComponents for
a Bill is actually BillValue for the Bill. You will have to determine if
that is true. If it is, then you'd be better not having the BillValue field
in the Bill table.

Employee table:
EmpId PK - autonumber
EmpCode Payroll ref or Initials or whatever - text
Usual Extn Office extension they usually work from??
Name, address etc etc

PersonalUse table
CallId PK - autonumber
BillId FK into Bill table - long
EmpId FK into Employee table - long
Assessed assessed value of use - currency

If that is a workable table structure, then you might have a form for Bill
that has two subforms:
one showing each BillComponent and a summary value and the other showing
PersonalUse and a summary value.

Only because it hasn't been mentioned, I should say all this PK and FK logic
can be formalised - once the tables have been defined - using Tools
Relationships. If you need it, there's tons of info on that in the
newsgroups.

Good luck
CD

 

[rudwan]

mr chaldon
i found a replay from you , but i fail to review it ,
because it give me blank comment
could you please replay post , and i will ne thanking you
too mich

 

[rudwan]

ye sjust now i success to read your replay
it is a wonderfull flowchart , it is exactly what i want
but there is a remark :
you said :

( There is a case to make that the sum of CompValue for
all BillComponents for

a Bill is actually BillValue for the Bill. You will have to determine if
that is true. If it is, then you'd be better not having the BillValue field
in the Bill table. )

who that ? who i can get the billvalue amount ? by query
then i cannot add any flag to ( checked-paid ) fields
beacuse it will be derived bu group query which will be
only unupdatable

 

[Chadlon]

Hi,

The suggestion is certainly _not_ to dispense with the Bill table; you are
right to insist that the record remains, since it is in the Bill table that
the BillChecked, BillPaid and so on values reside.

It is simply that if you have a form into which you can enter new (and edit
existing) Bill records, then part of the function of that form will be to
sum the CompValue fields in the linked rows of the BillComponent table.
If it turns out that the sum of those fields is - always and ever - exactly
related to (or even equal to) the BillValue, then BillValue becomes a
redundant item. As in, you do not need it because you can compute the value
from elsewhere.

In your case, it has the added benefit that you can be sure that the
management analyses based on the BillComponent records will necessarily
correlate with the financial records - nominally based on the Bill table -
because they are quite literally based on the same figures. Yet another
benefit is the 'autocheck' effect at data entry ... where a correlation
between the bill value of the phone bill and the summation of the detail
lines confirms that all details have been entered.

Now your point about including a sum in a recordset resulting in the
recordset becoming 'read-only'. If we assume for the present purpose that
this is correct, it does not matter. Because that is not the query we are
using.

If the main form is based on the Bill table (where Bill table does not
include the BillValue field), then a subform displaying the related
BillComponent records can deliver a sum of the CompValue fields back to the
main form - if that's where you want to display it.

Even if you don't want to display the subform, you could use a domain
aggregate function to an unbound text item to display the summation.
Something like:
ControlName: calc_BillValue
ControlSource:
=DSum("CompValue","BillComponent","BillId="&Cstr([BillId]))

In any other situation where you need the summation for BillValue - enquiry,
reporting etc - you can use the accumulation query that you appear already
to be familiar with.

So, the key element to get you moving forward again is to base the Bill form
on the Bill table (or a filtered view of) and not on a summation.

Good luck
CD

 

[rudwan]

good eveining :
first of all i'm thanking you too much for your kind help
now the idea is :
component(456123) = 250 $ official calls
+100 $ personel ( jack)
__________
total amount for telephone (456123) = 350


component(123456) = 300 $ official calls
+200 $ personel ( sam)
___+100 $ personel ( jan)
_______________________________

total amount for telephone (123456) = 600


component(789123) = 900 $ official calls
+200 $ personel ( rey)

+200 $ personel (jim ) _
____________________________________________________
total amount for telephone (789123) = 1300



total bill value for accno ( 99999 ) = 300+600+1300
say 2200 $
i will post billcomponent > its sum will give total
telephone amount
total of (total telephone amount ) will give billvalue
both of (total telephone amount ) & (billvalue ) can be
derived by summing the entries .
but , for controlling prposes , i want to store both of
billvalue , and (telephone amount) , because when i want
to post a new bill , i have to review its historical
transactions , in addition , after posting the bills , i
have to add some flags to the table < who it will be ?

-----Original Message-----
Hi,

The suggestion is certainly _not_ to dispense with the Bill table; you are
right to insist that the record remains, since it is in the Bill table that
the BillChecked, BillPaid and so on values reside.

It is simply that if you have a form into which you can enter new (and edit
existing) Bill records, then part of the function of that form will be to
sum the CompValue fields in the linked rows of the BillComponent table.
If it turns out that the sum of those fields is - always and ever - exactly
related to (or even equal to) the BillValue, then BillValue becomes a
redundant item. As in, you do not need it because you can compute the value
from elsewhere.

In your case, it has the added benefit that you can be sure that the
management analyses based on the BillComponent records will necessarily
correlate with the financial records - nominally based on the Bill table -
because they are quite literally based on the same figures. Yet another
benefit is the 'autocheck' effect at data entry ... where a correlation
between the bill value of the phone bill and the summation of the detail
lines confirms that all details have been entered.

Now your point about including a sum in a recordset resulting in the
recordset becoming 'read-only'. If we assume for the present purpose that
this is correct, it does not matter. Because that is not the query we are
using.

If the main form is based on the Bill table (where Bill table does not
include the BillValue field), then a subform displaying the related
BillComponent records can deliver a sum of the CompValue fields back to the
main form - if that's where you want to display it.

Even if you don't want to display the subform, you could use a domain
aggregate function to an unbound text item to display the summation.
Something like:
ControlName: calc_BillValue
ControlSource:
=DSum("CompValue","BillComponent","BillId="&Cstr ([BillId]))

In any other situation where you need the summation for BillValue - enquiry,
reporting etc - you can use the accumulation query that you appear already
to be familiar with.

So, the key element to get you moving forward again is to base the Bill form
on the Bill table (or a filtered view of) and not on a summation.

Good luck
CD

ye sjust now i success to read your replay
it is a wonderfull flowchart , it is exactly what i want
but there is a remark :
you said :

( There is a case to make that the sum of CompValue for
all BillComponents for have
to determine if

who that ? who i can get the billvalue amount ? by query
then i cannot add any flag to ( checked-paid ) fields
beacuse it will be derived bu group query which will be
only unupdatable

.

 

[Chadlon]

Hello again,

The set of examples you provide does not quite seem to tie in to available
information.

Given that the start point is a phone bill, the phone company do not know
which of the calls is personal or business.
What I mean is, the first figure you are dealing with is the total usage
figure from the bill, and then the breakdown by telephone number.

From a completely separate source - which has not been so far identified to
me, and need not be if you are happy with it - you have assessments or
estimates of personal use of phones. That information either does or does
not contain sufficient detail to allow you to correlate it with a specific
telephone

So, your raw data is (a) total use and (b) personal use. Therefore to get at
the 'official use' figure you need to do a calculation (subtraction rather
than addition). What that calculation is - or more precisely, at what level
it occurs - is determined by the amount of detail you have in support of the
personal use.

Your examples suggest that you can get that detail on a per-telephone-number
basis. My previous schema suggestion was based on another model ... the
personal use information was linked to the Bill, whereas it would seem that
you would be able to link it to BillComponent. Not a major change...

remove the statement
The Bill table has a one-to-many relationship with PersonalUse
add the statement
The BillComponent table has a one-to-many relationship with
PersonalUse

PersonalUse table
CallId PK - autonumber
BillCompId FK into BillComponent table - long
EmpId FK into Employee table - long
Assessed assessed value of use - currency

So, as has been said, the derived amount BillValue is achieved by a sum of
CompValue.
The term you are using (telephone amount) is exactly equal to CompValue.
The derived amount (official calls) is achieved by a sum of Assessed
(personal use) subtracted from CompValue.

That's the basic maths, as I understand it.

Any other flags, dates, notes etc that you need to add can be done - to the
appropriate record(s) - without impacting the calculations.

CD

 

[rudwan]

hi
regarding who i can get the personel use , i will tell you
who i'm recieving the bills <
telecom company submit main bill like this :
accno billdate bill value

details :
callNo outgoing call duration amount
callNo outgoing call duration amount
callNo outgoing call duration amount
callNo outgoing call duration amount
callNo outgoing call duration amount
callNo outgoing call duration amount
_______________________________________
teleph no teleph value

dont car about who i can get the personnel use , it come
by checking calls , which is official is official , which
is personnel will be personnel
now , you already descibed the correct flow chart , i will
accept it as fit with what i am looking for ,
but now i need one thing :
what will be the best design for the form ? which will be
in my suggesion :
main form for ( bill information - billID-BillDate-etc )
sub_form (datasheet design - to post in each row :
teleph no - amount - )
sub_form total will be billValue ok
now , as per that design , i dont know where i will inclode
bill component table to get personnel use details ?

 

[Chadlon]

I think we are taking this outside of the scope of interest of the newgroup.

If you want to pursue the conversation, send me an e-mail via the enquiry
page
on http://www.realcom.co.uk/

That way, neither your e-mail address nor mine becomes public.

CD