Combining records from one table

[SimplyES]

I have a table that contains records of job allocations with fields for Date, Job number, Client name, Site name, Engineer's initials.

Currently, if a job is allocated to a team of, say, three engineers, there will be three records for each day of the job duration - one record for each engineer - with all fields holding the same data except for the date field and the field which holds the engineers initials. So, a job with a duration of three days would have three records for each of the three days.

I would like to combine the details for display purposes so that the common data is only shown once per day and the Engineer field shows collated data of the initials from all three engineers (eg BL/MF/VA).

Is there a tidy way to do this with a SELECT query or is it only possible using a loop to scan and extract/concatenate into a new table/cursor?

 

[Mike Lewis]

I can't see any way of doing with a SELECT. That's partly because you have an arbitrary number of engineers per job. You mention three engineers, but the number could presumably be anything from one to an arbitrary high number. The other difficulty is that you want to end up with a single field that combines data from a field in an arbitrary number of records.

Unless someone comes up with a better idea, I think you will have to use a looping mechanism. Off the top of my head, it could look something like this:

1. Start by doing a SELECT that will get all your data into a cursor, ordered by engineer within date within job number. Give that cursor an extra field, to hold the concatenated engineers' initials (that field with be blank at this stage).

2. Loop through the cursor. At each change of job / date, write the current record's data to new record in a second cursor.

3. For records where this no change of job / date, concatenate the record's engineer into the new field in the last-written record in the second cursor.

4. Put the second into the desired order and either write it your permanent storage or use it as input to your report (or whatever else you want to do with it).

As I said, this is all off the top of my head. You will need to fill in the details for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

You could use a function to return all the engineers in a string

FUNCTION GETALLENGINEERS
	PARAMETERS m.JOBDATE,m.JOBNO
	PRIVATE m.JOBDATE,M.JOBNO, m.OLDAREA, m.OLDRECNO, m.OLDORD, m.STRING
	m.OLDAREA = SELECT(0)
	SELECT MYTABLE
	m.OLDRECNO = RECNO()
	m.OLDORD	= ORDER()
	SET ORDER TO DATEANDJOBNO
	** assuming DTOS(DATE)+JOBNO is an index
	m.STRING = ""
	SEEK (DTOS(m.JOBDATE)+m.JOBNO)
	DO WHILE .NOT. EOF() .AND. DTOS(DATE)+JOBNO = (DTOS(m.JOBDATE)+m.JOBNO)
		IF !EMPTY(m.STRING)
			m.STRING = m.STRING + ", "
		ENDIF
		m.STRING = m.STRING + ENGINEER
		SKIP
	ENDDO
	SET ORDER TO (m.OLDORD)
	IF m.OLDRECNO > 0 .AND. m.OLDRECNO <= RECCOUNT()
		GOTO m.OLDRECNO
	ENDIF
	SELECT (m.OLDAREA)
	RETURN(m.STRING)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[SimplyES]

Thanks chaps. I pretty much thought that was the way this one would roll but I didn't want to miss a trick if there was one. Thanks for the suggestion - I'll work with that.

 

[Mike Lewis]

Lewt us know how you get on, and come back if you need help filling in the details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Even in T-SQL this needs a bit of trickery with XML functionalities VFP doesn't have this way. Concatenation (whether simple or in the form of comma separated values list) is no usual SQL aggregation function, he job of SQL is to get the data, not to format it, that's the job of forms or reports.

The only usual way to report this without listing repeated values is by specifying "print repeated values: no" in "print when" tab of report field properties.
You'd still print the lines this way, though and the initials would appear in 3 lines.

So indeed in VFP it's most likely the simplest you query the data as you do and then comnine into a new cursor.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[JRB-Bldr]

one record for each engineer - with all fields holding the same data except for the date field and the field which holds the engineers initials.

It sounds like your data isn't adhering to good Normalization standards.
That alone introduces additional challenges in gathering the data into a useful manner for Reports, etc.

You might want to consider re-designing your data architecture to a more Normalized manner.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

One other small suggestion:

In your example, you show the three engineers' initials separated by a forward slash, like this: BL/MF/VA. You should consider using a space instead: BL MF VA.

This would have the advantage of being more readable, especially if there is a long column of this data. It would also make it very slightly easier to program, as you wouldn't have to worry about removing the final forward slash.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SimplyES]

Mike: Quite right, and I do plan to use spaces, for both the reasons you suggest. I just wanterd it to be clear in the text I submitted in my question.

JRB-Bldr: I think I'm OK on normalisation (but I will review!) - The fields I showed are actually one level up, from an cursor SELECTed in preparation for a different version of the data display where the engineers do need to show as individuals. I just need to decide whether to start from scratch for this new view or convert the first selection as needed. It's likely there will be a lot of switching between the two views.

These two views also need to be able to 'move back and forth' across weeks. Is it better to SELECT all records and filter for the display or SELECT (and keep re-SELECTing) just for the weeks currently displayed as the user moves from one week or month to another? I should say that, because the grids have a multi-field cell (that was fun to work out!), I have a separate grid to display each of the 7 days of the week. That means 7 cursors to SELECT and/or filter. At the moment I am SELECTing (28 days at a time) and it's performing fine. Of course, if a user edits the data, a re-SELECT for display has to happen anyway.

 

[SimplyES]

In the end I included the script to combine the Engineers initials as part of the main selection process as an optional path, with a button on the main display form so that the user can switch between the two. Works a treat and, now that I see that working, I think they will likely use the switch quite a bit.

Thanks chaps.

 

[GriffMG]

Do you mean my little effort, but without the commas?

BTW, be very careful thanking chaps on here, not everyone is a chap - and I got told off a while ago for doing something similar.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​