Sequential Records

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I have a database where each row is activity for a specific date for a
customer. Each row consists of amongst other things, a unique ID, a customer
ID, an activity date and the outcome of that activity.

I need to create a table that has a row for each customer ID. Then there is
a field for Activity Date (1), Activity Outcome (1), Activity Date (2),
Activity Date (2), Activity Date (3), Activity Date (3) etc...

1,2,3 etc need to be arranged in chronological order. So I would need to
transpose this:

Unique_ID Customer_ID Activity_Date Activity_Outcome
1 85 01/01/2009 10
2 85 01/02/2009 20
3 85 01/03/2009 30


to this

Customer_ID Activity_Date(1) Activity_Outcome(1) Activity_Date(2) Activity_Outcome(2) Activity_Date(3) Activity_Outcome(3)
85 01/01/2009 10 01/02/2009 20 01/03/2009 30
 
What you have described is a spreadsheet, not a relational database.
What you need is a table for customers and another for activities pertaining
to those customers in a one-to-many relattionship. You might even have
customer-incident-activity for three table related as one-to-many if the same
customer may have multiple incidents.

Create the relationship between the primary key field of the 'one' table to
the foreign key field of the 'many' table. Select Referential Integerity and
Cascade Update for the relationship.

Use a form/subform with Master/Child links set using the primary/foreign key
fields. When you add a record to the subform it automatically inserts the
key in the 'many' table field.
 
I see your point and agree, however my current table is an output from a
system that I am unable to change as it is an output file and the data is not
entered via my .mdb. Not an option unfortunately.
 
You can use union and append queries to normalize the data from a flat file.

SELECT 1 AS Unique_ID, Customer_ID, [Activity Date (1)] AS Activity_Date,
[Activity Outcome (1)] AS Activity_Outcome
FROM YourTable
UNION ALL SELECT 2 AS Unique_ID, Customer_ID, [Activity Date (2)] AS
Activity_Date, [Activity Outcome (2)] AS Activity_Outcome
FROM YourTable
WHERE [Activity Date (2)] Is Not Null
UNION ALL SELECT 3 AS Unique_ID, Customer_ID, [Activity Date (3)] AS
Activity_Date, [Activity Outcome (3)] AS Activity_Outcome
FROM YourTable
WHERE [Activity Date (3)] Is Not Null
..... for as many as your maximum ...;
 
Had considered that, but one customer has 79 records in my output file (and
this may grow each week of that a new extract is provided for). Guess it's
bad output design and no realistic manipulation that I can perform. Thanks
for the feedback so far...

KARL DEWEY said:
You can use union and append queries to normalize the data from a flat file.

SELECT 1 AS Unique_ID, Customer_ID, [Activity Date (1)] AS Activity_Date,
[Activity Outcome (1)] AS Activity_Outcome
FROM YourTable
UNION ALL SELECT 2 AS Unique_ID, Customer_ID, [Activity Date (2)] AS
Activity_Date, [Activity Outcome (2)] AS Activity_Outcome
FROM YourTable
WHERE [Activity Date (2)] Is Not Null
UNION ALL SELECT 3 AS Unique_ID, Customer_ID, [Activity Date (3)] AS
Activity_Date, [Activity Outcome (3)] AS Activity_Outcome
FROM YourTable
WHERE [Activity Date (3)] Is Not Null
.... for as many as your maximum ...;


--
Build a little, test a little.


Andy said:
I see your point and agree, however my current table is an output from a
system that I am unable to change as it is an output file and the data is not
entered via my .mdb. Not an option unfortunately.
Click to expand...
Click to expand...
 
Back
Top