Using a query for a simple calculation -- maybe not so simple

[Guest]

I use access for maintain a modified ledger. It tracks check numbers, dates,
job addresses, vendors and amounts. I need to set it up so that if there is
multiple jobs associated with one check number, I can allocate portions of
the total amount of the check to each respective job. I have the table set
up so that the "Job" field can have multiple entries, so on the query for
that table it contains a "Job" field and a "Job.Value" field. So, in my
current situation, I only need to figure out how to input different amounts
for each job.value, and have only the total for multiple jobs show up in the
table. Make sense??? I am by no means an access whiz. Thanks.
Jim

 

[Guest]

Create Check table with these fields minmum --
Checks --
CheckID - Autonumber - primary key
CheckNUM - text
CheckDate - DateTime
Writer - text
Amount - currency

Create a Jobs table with you jobs information and have a primary key
(Autonumber) field named JobID.

Then a Disbursement table.
Disbursement --
DisbursementID - Autonumber
CheckID - integer - foreign key
JobID - integer - foreign key
Remarks - text or memo

Create a one-to-many relationship from Checks to Disbursement on the CheckID.
Create a one-to-many relationship from Jobs to Disbursement on the JobID.

Use a form (Check) and subform (Disbursement) to enter the allocations

You can add an AfterUpdate event to check if you over allocate the check by
checking the sum of all allocation greater than check.

 

[Larry Linson]

I use access for maintain a modified ledger. It tracks check numbers,
dates,
job addresses, vendors and amounts. I need to set it up so that if there
is
multiple jobs associated with one check number, I can allocate portions of
the total amount of the check to each respective job. I have the table
set
up so that the "Job" field can have multiple entries, so on the query for
that table it contains a "Job" field and a "Job.Value" field. So, in my
current situation, I only need to figure out how to input different
amounts
for each job.value, and have only the total for multiple jobs show up in
the
table. Make sense??? I am by no means an access whiz. Thanks.

You need a related table, to show applicable jobs that apply to a particular
check... each record would have a "foreign key" to the related check,
identification for the job, and amount or percentage. If you are keeping the
amounts in the related table, then you should sum them when you use the
data, rather than keeping a (redundant) total in the check table.

To use in a friendl manner, create a Form for the check with a Subform
Control containing a Form with the applicable Jobs.

Larry Linson
Microsoft Access MVP

 

[Guest]

I really appreciate the help, but thats not exactly what I need. It is very
close. I see where you are going with the relationships between tables.
However, I would like to be able to have the input process for this data be
as simple as possible and I am not sure if it is the way we are doing it now
-- but then again I may just be a little slow! Anyway...to my understanding,
creating relationships allowed different jobs to be associated with one check
number. I have 2 questions about that;
1. I don't really understand the purpose of the ID's. Are the
autonumbered primary keys necessary (could I just create a separate table
called Jobs and have two fields called Job and Subtotal and use the sum of
the Subtotals to gain my check number total)?

2. If the above is not possible, what can I do so that all I see in the
table drop down menu for each check number is a field called "Job" and a
filed called "Subtotal" in which I can input my own values to achieve the
check number total.

THANKS SO MUCH! I apologize for being complicated!

JIM