Your description is too abstract to provide substantial answer. It seems that the answer would be to import (possibly just the name and GroupID?) directly into the other table but I have no idea without example file or a lot more details, sorry. :^)
i wasnt sure if i had given enough detail.
Table 1 - Individual's Info eg Name, Club, Number. The layout has a portal that then draws information relevant to this person from table 2. in the portal it has the last five entries for this person
Table 2 - Data Import table. This table imports data from an excel spreadsheet which is used to collect data. This spreadsheet has data that is updated weekly. Data includes Name, Club, then 4 x data fields relevant to their weekly performance. Some individuals will have a data record for each week, whilst others may not. There may be a new individual in one week that has not had a previous record.
eg week 1 Name Date Club Field x Field y Field z
john Smith 230311 A 6 8 10
Alan Johns 230311 b 8 5 12
Brad Brown 230311 A 5 10 4
week 2 Name Date Club Field x Field y Field z
john Smith 300311 A 4 3 10
Alan Johns 300311 b 10 6 3
Greg White 300311 A 6 7 4
The two tables are linked by name. How do i get it to work so that a new record is created for the new individual in Table 1 (eg Greg White), without creating duplicate records for those that already have one (eg John Smith, Alan Johns)? i dont want to do it manually as there are over 800 records already (there will be close to 4000 at its maximum) in table two and i would prefer not to have search manually for new entries each week.
Thanks for any assistance.
I realize it may be difficult to change an existing process but I am worried that you are using the individual's name as the unique entry for table1. What if you get two John Smiths? How will you tell them apart?
Anyway, one answer is the set the name field in the Individuals table to validate as unique, i.e. go to Name field and select Options and go to Validation tab. Under first section 'Validate data in this field' choose 'always' , uncheck 'allow user to override' and then select under Require 'Unique Value.'
Do not put anything else (such as a message) since you want this automatic when you import. Now take your file which you will be importing into table 2 and import into table 1. In this instance, you don't need to show all records in table 1 first. Specify only 'Add new records' and select only the name.
If validation fails on this import, any records that exist (failed validation) will not import. Only unique names will import into your Individuals table.
As an aside ... I truly wish you could find a way to use unique IDs in the Individual table and use those IDs to write back to the import table. Names are truly not dependable for the reason I indicated above AND because someone can change their name (ask any young lady who recently married).
Anyway, I hope this helps. Let me know if I've missed the mark. :^)
Thanks LaRetta - Your solution seems to have worked for me!
I have considered your suggestion about using a serial/unique code - is there a way that this can be done retrospectively such that it wont break what now appears to be working?
You current have an existing relationship between Individuals and Imports based upon name so a transition would be easy. It would be as follows:
In your Individuals table, create an auto-enter serial (type of number) increment by 1 called IndividualID (or whatever you wish). Then go to a layout based upon Individuals. Show All Records and then unsort them. Place your cusor in this new IndividualID field and select from menu (back up first) ... Records > Replace Field Contents ... select 'replace with serial numbers' initial value of 1 and increment by 1. Be sure it is CHECKED to update serial number in Entry Options.
You now have your Individuals serialized and any new records added (if you import be sure to specify 'perform auto-enter') will have the next serials in line.
Now go to your Import table. Add a field called IndividualID (type is number). Show all records and place cursor in this field and Replace Field Contents but this time, replace by CALCULATION which would be pointing at your Individuals table and the new IndividualID in it.
If your imported data in table 2 won't have this new serial, you will need to leave the existing relationship. Then import into Individual first. Then import into import table and, after your import and while you still have your found set, run Replace Field Contents again to set the newly imported records with their proper IndividualID from Individuals.
In the future, this ID should be incorporated in the imports as well to eliminate the issue of two people having same name.
Perfect - works as you suggested - many thanks!