Combining Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to track jobs, and beed to be able to provide a 'snapshot' for our MD which combines all the jobs for one person on the same day, and shows all the dates in the month

e.g.
|_________|___Staff1___|___Staff2___
| 20/04/04 | Job1, Job2 | Job 1, Job 2
| 21/04/04 | Job 1 |
| 22/04/04 | |
| 23/04/04 | |
| 22/04/04 | | Job 1

I have a source table which shows all jobs for all staff, but need to be able to combine the data from multiple records/fields into

Can this be done?
 
Nik

Since queries are based on underlying data (from tables or other queries),
it will be a little tough to recommend an approach to consolidating multiple
rows/fields without knowing where we're starting...
 
Jeff (or others),

I am trying to do the same thing. I have a MemberID and four fields that are Yes/No. Each MemberID may have varying combinations of Yes/No for those fields; that is, MemberID #1001 might have (-1,0,0,0) while MemberID #1002 might have (0,-1,-1,0).

If MemberID #1002 has (0,-1,-1,0) for the Yes/No values in the four fields, the underlying table is keeping those as two separate records: (0,-1,0,0) and (0,0,-1,0).

How do I create a query/temp table that will take those multiple records and combine them into one record?

I thought about update queries, but each MemberID can have different numbers of records depending upon how many (-1) records there are.

Thanks!

Ross Erickson
 
You could use a Totals query.

SELECT MemberID,
Sum(Field1)<>0 as Fld1,
Sum(Field2)<>0 as Fld2,
Sum(Field3)<>0 as Fld3,
Sum(Field4)<>0 as Fld4
FROM YourTable
GROUP BY MemberID
 
Back
Top