×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Combining records from one table

Combining records from one table

Combining records from one table

(OP)
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?

RE: Combining records from one table

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

RE: Combining records from one table

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

CODE

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 Smileing

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.

RE: Combining records from one table

(OP)
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.

RE: Combining records from one table

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

RE: Combining records from one table

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

RE: Combining records from one table

Quote (SimplyES)

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

RE: Combining records from one table

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

RE: Combining records from one table

(OP)
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.

RE: Combining records from one table

(OP)
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.

RE: Combining records from one table

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 Smileing

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close