Combining records in a single table

[Grunners]

Hi all,

I have a table as follows:

InvoiceID (PK) - Autonumber
MemberNo - text
AgencyName - text
Hours1 - number
Rate1 - currency
Hours2 - number
Rate2 - currency
TotalHours - number
GrossPay - currency

The InvoiceID is the only unique field. This is because the Members may be input a number of times - they can work for different agencies in any one week and their amounts for each agency will differ.

Most members will only work for one agency and thus have just one entry - easy. It's the members that work for more than one agency I'm having the problem with.

How do I combine 2 or more rows (i.e. the same member number but with different hours and rates) in to just one row that is a sum of all their hours1/rate1/totalhours/grosspay etc?!?

I'm really banging my head against the wall now so ANY ideas would be more than appreciated! There is also the possibility that I've got the structure of this table wrong and need to divide it up somehow. Anything!!!

Many many thanks in advance
Grunners

 

[RichMorrison]

You probably don't need to "combine 2 rows".

If you want the total for a Memeber, create a query with Group By on Member.

HTH,
RichM

 

[louchey]

I recommend structuring your tables something like this.........

Split your tables into 2.
If the "MemberNo" is the persons name, then have one entry per person in the main table. I would use this as your primary key.
All other fields can go in table 2.
Create a relationship between the 2 using "MemberNo" as common field.
Now create a form and subform ( Use the wizard function if your not too sure) and hey presto !!!
hope some of this helps

Louchey

 

[Grunners]

Thanks guys,

For a fleeting moment there i thought i had it licked! I should have mentioned in my first post that i need to 'combine' the entries in to one as they then go in to a payroll system.

A member can only be paid once in a week so if he has say 3 entries they all need to be added up and sent to payroll as one record. In the payroll system the member no is unique and for good reason.

I understood the point about doing a select query and summing the totals - i might experiment with this a bit more.

At present there is no database to do this. It's all done in excel but is very error prone. What about exporting to excel, doing the sums, and then importing back to access? Just seems a bit long winded to me. Surely VBA should be able to do this. I'm no expert but it must be able to say "take all the records where the count of member number is >1, sum up all corresponding fields, and create a new record in the table (or any table)"

Hope i'm making sense!?

It's always the things you think will be easy that are, ahem, really really difficult...

Thanks again
Grunners

 

[Grunners]

Cheers Pat

I thought that I'd have to go down the query road. Think this was just me trying to make things a bit too flash!

I have resolved the problem and I'll read up on what you said about putting calculated totals in a table.

Thanks all - very much appreciated.

Grunners