calculation

[smason]

hello i have a tbl that contains the following

jobid split1con splitcon £ splitcon2 splitcon £

11 john 300 bob 200
13 bob 200 john 100

i need the calculate all the values for i.e john in a total (in this case
400) how would i do this!

 

[Douglas J. Steele]

The fact that your table isn't properly normalized makes this far more
difficult than it need be.

You've got what's referred to as a repeating group. Rather than having
split1con, splitcon £, splitcon2 and splitcon £ as separate fields in a
single row, those should be two rows in a separate table. What are you going
to do if you need to split the job three ways instead of two?

You really should redesign your tables. In the meantime, though, you can
create a query that will mimic how the tables should actually be using a
Union query:

SELECT jobid, 1 AS WhichCase, split1con AS splitcon, [split1con £] AS
[splitcon£]
FROM MyTable
UNION
SELECT jobid, 2 AS WhichCase, split2con, [split2con £]
FROM MyTable

Now, it's a simple matter to create a query based on that query:

SELECT splitcon, Sum([splitcon£])
FROM MyQuery
GROUP BY splitcon

 

[Allen Browne]

Your table contains columns like you would use in an Excel spreadsheet.
That's not the right approach to use in a relational database.

It seems that one job can be split up between 2 (or more?) contractors.
Therefore you need a Job table (with the job header info), and a JobDetail
table (with the info about the contractors.)

Contractor table (one record for each contractor), with fields:
ContractorID primary key
Surname
Firstname

Job table (one record for each job), with fields:
JobID primary key
ClientID who this job is for (relates to a table of clients)
JobDate date of the job
JobComment memo explanation of what's to be done.

JobDetail table (one record for each line item in the job). Fields:
JobDetailID primary key
JobId Number which job this is a line item of
ContractorID Number which contractor does this part of the job
Charge Currency how much the contractor gets for this
part.
Descrip text what this part of the job is.

The will be a Client table as well.

If that's a new concept, open the Northwind sample database that installs
with Access, and open the Relationships window. Look particularly at how the
Orders and OrderDetails tables fit together, so one order can have many line
items.

That's the most fundamental and important thing you need to learn to design
a database in Access: how to use the one-to-many relationship.

 

[Bill Mosca]

Your table is not normalized. That is why this task is difficult. There
should not be 4 fields, only 2.
It should be JobID, firstname, amount

That would give JobID 2 records and much easier to total.

But as your table is now, you can do it using a UNION query.

SELECT JobID, split1con AS MyName, [splitcon £] As Amount
FROM MyTable
UNION
SELECT JobID, splitcon2 AS MyName, [splitcon2 £] As Amount
FROM MyTable

Save the query as Query1, and run a Totals query on it thusly:

SELECT JobID, MyName, Sum(Amount) as SumOfAmount
FROM Query1
GROUP BY JobID, MyName

 

[smason]

hello !

when you say two rows in a separate table how would you do that! !

please bear with me!

The fact that your table isn't properly normalized makes this far more
difficult than it need be.

You've got what's referred to as a repeating group. Rather than having
split1con, splitcon £, splitcon2 and splitcon £ as separate fields in a
single row, those should be two rows in a separate table. What are you going
to do if you need to split the job three ways instead of two?

You really should redesign your tables. In the meantime, though, you can
create a query that will mimic how the tables should actually be using a
Union query:

SELECT jobid, 1 AS WhichCase, split1con AS splitcon, [split1con £] AS
[splitcon£]
FROM MyTable
UNION
SELECT jobid, 2 AS WhichCase, split2con, [split2con £]
FROM MyTable

Now, it's a simple matter to create a query based on that query:

SELECT splitcon, Sum([splitcon£])
FROM MyQuery
GROUP BY splitcon

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

hello i have a tbl that contains the following

jobid split1con splitcon £ splitcon2 splitcon £

11 john 300 bob 200
13 bob 200 john 100

i need the calculate all the values for i.e john in a total (in this case
400) how would i do this!

 

[Douglas J. Steele]

Allen and Bill have given you the details.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

hello !

when you say two rows in a separate table how would you do that! !

please bear with me!

The fact that your table isn't properly normalized makes this far more
difficult than it need be.

You've got what's referred to as a repeating group. Rather than having
split1con, splitcon £, splitcon2 and splitcon £ as separate fields in a
single row, those should be two rows in a separate table. What are you
going
to do if you need to split the job three ways instead of two?

You really should redesign your tables. In the meantime, though, you can
create a query that will mimic how the tables should actually be using a
Union query:

SELECT jobid, 1 AS WhichCase, split1con AS splitcon, [split1con £] AS
[splitcon£]
FROM MyTable
UNION
SELECT jobid, 2 AS WhichCase, split2con, [split2con £]
FROM MyTable

Now, it's a simple matter to create a query based on that query:

SELECT splitcon, Sum([splitcon£])
FROM MyQuery
GROUP BY splitcon

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

hello i have a tbl that contains the following

jobid split1con splitcon £ splitcon2 splitcon £

11 john 300 bob 200
13 bob 200 john 100

i need the calculate all the values for i.e john in a total (in this
case
400) how would i do this!