Mutiple columns into one column

[ChimpZter]

Hi,

I have a table with equipment in the first column and their maintenance dates in the subsequent 3 columns serviceDate, 2 and 3

How do I transform this table from 4 columns into just 2 columns; i.e. equipment and serviceDate only, obviously equipment names will be repeated in the first column many times over?

This has had me pondering for a while now so could anyone help please?

thanks

John

 

[Galaxiom]

Equipment names should be stored in a table against a Primary Key (say EquipmentID).
This key should be a foreign key in a ServiceDate table and the dates recorded in individual records against it.

Use a series of append queries to add the records from each of the old service date fields to the single date field against multiple instances of the EquipmentID.

 

[John Big Booty]

First up you might want to read up on Normalisation as what you are attempting would appear to violate this principal.

You can concatenate a number of fields in to another filed by using the Ampersand (&) operator, code would look something like ;

= Me.FirstField & ", " & Me.SecondField

NOTE; This should be done for display purposes only, Concatenated fields should never be stored.

 

[boblarson]

First up you might want to read up on Normalisation as what you are attempting would appear to violate this principal.

Actually, it would appear to me that he is moving to a normalized form (from 4 fields to 2 and a record for each date for each equipment instead of more than one date field per equipment record.

 

[John Big Booty]

Actually, it would appear to me that he is moving to a normalized form (from 4 fields to 2 and a record for each date for each equipment instead of more than one date field per equipment record.

He did say;

........obviously equipment names will be repeated in the first column many times over?

though, which prompted my opening remark

 

[boblarson]

He did say;

though, which prompted my opening remark

Well, that part should be replaced by the Equipment ID and not the name, so have to give you half credit

 

[ChimpZter]

Equipment names should be stored in a table against a Primary Key (say EquipmentID).
This key should be a foreign key in a ServiceDate table and the dates recorded in individual records against it.

Use a series of append queries to add the records from each of the old service date fields to the single date field against multiple instances of the EquipmentID.

Just need to clarify - I agree with what you are saying I just need to get the data in that format! It has worked out that way because I am using data from elsewhere and I added the service frequency (days) to the last scheduled date so I could project forward when the next services would be due (i.e. serviceDate1 = lastScheduledDate + freq; serviceDate2 = lastSchduledDate + freq * 2 etc). I don't want to concatenate; would a UNION query be suitable?

 

[boblarson]

Just need to clarify - I agree with what you are saying I just need to get the data in that format! It has worked out that way because I am using data from elsewhere and I added the service frequency (days) to the last scheduled date so I could project forward when the next services would be due (i.e. serviceDate1 = lastScheduledDate + freq; serviceDate2 = lastSchduledDate + freq * 2 etc). I don't want to concatenate; would a UNION query be suitable?

Yes, you can use a Union query to get the information.

 

[wilpeter]

If you stick with Append queries:
First thing, your Primary Key should be turned off temporarily. Each Update Query would follow the pattern of creating a field called ServiceDate: (ServiceDate2) and would have criteria of Is Not Null.
The other fields would be Keyfield, and Equipment.
After appending, turn your Primary Key on...I hope it isn't AutoNumber.

 

[Galaxiom]

serviceDate1 = lastScheduledDate + freq; serviceDate2 = lastSchduledDate + freq * 2 etc

In this case you would not normally prepopulate a table with the subsequent dates but calculate them on the fly from the date of the last service.

Only when the service was actually carried out would you create a record.

 

[ChimpZter]

Yes, you can use a Union query to get the information.

okay cokey thanks,

well would this work ....

SELECT [Equipment],[ServiceDate1] as [Equipment],[ServiceDate]
FROM [MyTable]
UNION SELECT [Equipment],[ServiceDate2] as [Equipment],[ServiceDate]
FROM [MyTable]
UNION SELECT [Equipment],[ServiceDate3] as [Equipment],[ServiceDate]
FROM [MyTable]

 

[Galaxiom]

SELECT [Equipment],[ServiceDate1] AS [ServiceDate]
etc

(The AS needs to be applied individually to each aliased field. It is not required for the Equipment field.)

I would normally use UNION ALL as this is faster.
Without ALL the records are all tested for repeats which is great if there is any danger of that.
But if you are sure there are no repeats it just makes extra unnecessary work for Access.

However I would emphasise my earlier post suggesting that the records not be created until the service is carried out. If you need to distribute some kind of document with this information before the services are completed it should be created in a query and that query exported.

 

[ChimpZter]

SELECT [Equipment],[ServiceDate1] AS [ServiceDate]
etc

(The AS needs to be applied individually to each aliased field. It is not required for the Equipment field.)

I would normally use UNION ALL as this is faster.
Without ALL the records are all tested for repeats which is great if there is any danger of that.
But if you are sure there are no repeats it just makes extra unnecessary work for Access.

However I would emphasise my earlier post suggesting that the records not be created until the service is carried out. If you need to distribute some kind of document with this information before the services are completed it should be created in a query and that query exported.

Thanks, not sure what you mean by the second para there but the first bit has done the trick - I have learnt something new today!!

Just for the record this worked....


SELECT [Equipment], [NextSchedDate] AS [ServiceDate]
FROM [myTable]
WHERE [NextSchedDate] >= #04/01/2010# and [NextSchedDate] <= #03/31/2011#

UNION ALL SELECT [Equipment],[NextDate1] AS [ServiceDate]
FROM [myTable]
WHERE [NextDate1] >= #04/01/2010# and [NextDate1] <= #03/31/2011#

UNION ALL SELECT [Equipment],[NextDate2] AS [ServiceDate]
FROM [myTable]
WHERE [NextDate2] >= #04/01/2010# and [NextDate2] <= #03/31/2011#

UNION ALL SELECT [Equipment],[NextDate3] AS [ServiceDate]
FROM [myTable]
WHERE [NextDate3] >= #04/01/2010# and [NextDate3] <= #03/31/2011#
ORDER BY [Equipment],[ServiceDate]

 

[Galaxiom]

Thanks, not sure what you mean by the second para

In good practice one does not store derived data in a table.
Since the subsequent scheduled service can be calculated from the date of the previous one there is really no point storing all them in a table.

When you want the date of the next service, calculate it on the fly by using something like:

In a query:

 NextDate: Max([mytable].[ServiceDate]) + [Equipment].[ServiceFrequency]

OR for a control source on a form:

 =DMax("[ServiceDate]","[mytable]","[EquipmentID]=Forms!formname.sometextbox") + DLookUp("[ServiceFrequency]","[Equipment]","[EquipmentID]=Forms!formname.sometextbox")

Or better still create the derived value in the Record Source of the form/report and display with a bound control.

Service frequency would be in Days.

When the service is actually done then the record would be written probably with a lot of other information about the procedure.