Forum Moderators: open
I basically want each PCT organization to have 7 records (positions), instead of just 2. All the cells/columns of those records can be the same, except for a few naturally unique ones: title, first name, surname, job title, and email address. All those unique cells are unknown except for the job titles; all 7 job titles are known.
My question is how do I duplicate a record in any of those organizations of type PCT 5 times, while ignoring the cells/columns that I mentioned and which must be unique, and at the same time add a different new job title in the Job Title cell of each of those 5 new records? And if this does not require VB scripting (which I don't know), can I also check if some of the organizations already have a record for one of those job titles (a few organizations have more than 2 records)?
Many thanks for anyone taking the time to help. Much appreciated.
Note, this requires the WHERE clause to bring back all the rows you want duplicated.
Then you can run the similar query 5 times with different 'jobtitle' hardcoded in there.
You are essentially duplicating the row, except throwing in a hardcoded value into one (or more) of the fields with this syntax.
I don't believe this is working, though. When I run the query, a message prompts for Ok or Help saying "Duplicate output destination 'Website'."
The code currently showing in SQL view is as follows:
INSERT INTO Contacts ( ContactTypeID, [Director of Commissioning], ContactID, Dear, Address, [City/County], PostalCode, Organisation, [Email Address], WorkPhone, WorkExtension, [General Email], Website, FaxNumber, Website )
SELECT [Contact Types].ContactTypeID, Contacts.[Job Title], Contacts.ContactID, Contacts.Dear, Contacts.Address, Contacts.[City/County], Contacts.PostalCode, Contacts.Organisation, Contacts.[Email Address], Contacts.WorkPhone, Contacts.WorkExtension, Contacts.[General Email], Contacts.Website, Contacts.FaxNumber, Contacts.Website
FROM [Contact Types] RIGHT JOIN Contacts ON [Contact Types].ContactTypeID = Contacts.ContactTypeID
WHERE ((([Contact Types].ContactTypeID)="PCT") AND ((Contacts.[Job Title])="Chair"));
The criteria to define all the records that I want to duplicate is for the record to have "PCT" as the Contact Type and maybe "Chair" for the Job Title (and this is just to control the number of duplications, so that it doesn't duplicate 2 or 3 rows within each organization of type "PCT" and I end up with anything from 4 to 14 records in total for each organisation, when I just want 7.
And again, I'd like the duplicated rows to have all the fields (cells?) with the contents of the field of the Chair person for instance, but to have unique fields like First Name, Last Name, Title, Email Address blank instead of copying their contents too, and to change the Job Title "Chair" to--for example--jobtitle1, then another query to change it to jobtitle2, etc, until I have 5 duplications.
I hope I was able to explain my objective clearly. Many thanks.
I was able to solve my problem using a different and much more efficient method than the one I wanted to use and consequently, asked here how I can use it. I am adding this method & details for the record, so that if any other beginners come across the same problem, they know how to solve it.
Basically, I discovered that I was supposed to ask myself what exactly I was trying to achieve. This part about "goals" or "objectives" have become a bit of a cliché, and because it is repeated a lot, sometimes we tend to overlook it. However, it was very important in this case, because upon pondering upon the question and using Access's Analyze Table function, I discovered that my database was not efficient, and was much bigger than it should be, because it relied on a single table that had many duplications. I also discovered that just the word "duplicating" or "duplicate" in my initial question meant that I was up to no good when it came to database efficiency.
I achieved my goal by dividing the database into a number of tables, and the two main and biggest tables were one for organizations and another for members of those organisations. Then I designed a form, made up of 2 forms: one main for Organization, and one subform for that organization's members. This way, I did not need to duplicate any information and exclude unique fields/cells for the members. The subform was ready to accept any new members, and it was always conveniently linked to the Organization's form and table. Later on, the design of query that can produce a spreadsheet-like table that shows everyone in the whole database (with duplications in the column of Organization) was easy.
Now I have another question about a search button in an Access 2003 form, because version 2003 doesn't have the convenient search functionality near the record numbers as in version 2007. But I will post it in its own thread to ensure relevance & consistency.
Thanks to everyone, and hope that sharing my discoveries was of help to beginners and even experts who may need to advise beginners in the future.
