I'd suggest a table for Jobs (one record per job) and a related table for JobDates. Then do the find in the Jobs table, using the related date field.
OK. I understand conceptually what you're suggesting, but am unclear how the relationship is setup, and how to populate the new table. Please guide this newbie.
In the Jobs table, make sure you have a JobID field, defined as Number, Auto-enter serial number. All the fields that describe a job should be also in this table. In the JobDates table have a JobID field, defined as Number, the date field and any fields that describe a particular "occurrence" of a job.
Define a relationship between the two tables as:
Jobs::JobID = JobDates::JobID
If you like, allow automatic creation of JobDates records (this will allow you to enter new JobDates directly in a portal). Place a portal to JobDates on a layout of Jobs.
As for populating, I am not sure what you have already. I think your current table could become the JobDates table and you just need to add the Jobs table. What do you have now that uniquely identifies a job (i.e. has the same value for multiple occurrences)?
To answer your question, in the "Jobs" table (I'm assuming this to be the existing, primary table that displays all fields?), I have a unique, serial number auto-entry "Contract #" field, one contract # per job. The date field I am currently using is "Date of 1st Task", is a repeating field with 6 repetitions. I can (and have thru my trials) populate six individual task1, task2... fields with a "Get Repetion" calc. So, I can go both ways.
Ok, if you now have one record per job, with a unique serial, then you can import your records into a new table in the same file. Import the unique serial and the repeating field, and in the import dialog choose to split the repeating fields into individual records. This will then be your JobDates table - almost ready to use.
Make sure you have a backup before you start, as it's really easy to mess it up.
The import went well, but didn't see the option to split the repeating record -- so I chose to bring over the six date field data from the Jobs table individual Task1, Task2...Task6 fields. All looks OK when viewing the table after the import.
So, for performing a Find of a range of dates, which field do I target? Is this a single, or a multiple request over all six date fields from within the portal?
You would have seen the option, if you had selected the repeating field as the source field to import.
The entire idea here is to create a separate record for each date - NOT six date fields in the same record (you already have that).
I suggest you try it again and pay attention to the Import Options dialog you'll see after mapping the fields and clicking Import.
It seems so close, yet... I did as you suggested and DID see the option for splitting into separate records, which I chose.
Reimporting did create individual task records for the one Job, and I see them all in Browse mode - Show all records.
However, when I go to the Layout (in Jobs) that has the Portal (from JobsDates) and perform my date range find to cover at least three occurances of different task dates, I only get back the first dated task. Stumped!
Found another fly in the ointment -- when I make a change in the Jobs data, it doesn't update the corrosponding records in the JobsDates table data. Will this mean that everytime I query a JobsDates, I will need to re-import or somehow otherwise intentionally update the data?
Once you have made the conversion, you should keep the dates only in the JobDates table (please refer again to my second post in this thread).
when I go to the Layout (in Jobs) that has the Portal (from JobsDates) and perform my date range find to cover at least three occurances of different task dates, I only get back the first dated task.
I am not entirely sure what you mean here. Searching the related date field (in a portal, if you are doing this manually) for a range should find all jobs that have at least one occurrence in that range.
You will only find one record for each such job - because (hopefully) there is only one job record for each job in the Jobs table. But each found job "carries" with it all its related dates in the portal.
Sorry, I've been away from the project for a few days due to a death in the family and other work committments. I'll go back and review all the notes and try to see where I went wrong.
After playing around a bit with tables and relationships and re-reading your suggestions, I did get the dates to display as I originally requested. Through the process, I've discovered I need to refresh my training/understanding of tables and relationships so I can better follow the logic and get this entire database project working as it needs to. Thanks for your patience in working with this novice.