Changing design from many-to-many to one-to-many

[snowboarder234]

It was determined that the periodicals in this database are now to be listed WITHOUT the editors, changing the structure from a many to many relationship to a one to one. So, now the only thing that is to be displayed in the form is the title and issue.

To achieve this I tried the following (on a test database):
1. Deleted the many to many relationship be delinking what was linked to the junction table which was tblJournal Editor and tblJournalTitle. The end result was that the editor data continued to display in the form.
2. Tried just out right deleting the the editor table. System said no, you can't do that.
3. Went through the editor table and, one by one, deleted each record, of which there were only 10 at this point. This caused ALL data to be deleted from the form, this thwarted my goal of preserving the existing title and issues that are currently displayed.

Worst case scenario I can just delete the name fields in the form -- expedient, but probably not prudent in the long run.



Any suggestions on how to proceed from here......?

[ssanfu]

Not sure what you are trying to do:
- still enter the editor but do not display on forms/reports? Remove associated controls from forms/reports.

- do not want editors in the dB at all? Delete relationships between tblJunctionTitleEditor, tblJournalTitle and tblJournalEditor, then delete tblJunctionTitleEditor and tblJournalEditor. Remove associated controls from forms/reports.

- still want editor, but only 1 editor per issue in tblJournalIssues? Add field JournalEditorID_FK to tblJournalIssues.

[snowboarder234]

"- do not want editors in the dB at all? Delete relationships between tblJunctionTitleEditor, tblJournalTitle and tblJournalEditor, then delete tblJunctionTitleEditor and tblJournalEditor. Remove associated controls from forms/reports." <----- When I tried this second option, I lost all my data. I restored from the backup and simply deleted the form controls for the editors. Not the most elegant solution but I'm running out of time and options. My dilemma now is that the search option was predicated on the author's last name. Consequently, now there is no alpha sort of any kind. The question now is: how do I create an alpha sort predicated on the form's title field? [P.S., if it helps any, the removal of editors was specific to the journals/periodicals, not the database as a whole. The categories of books and multimedia still contain authors/editors, based on a quick tutorial by a campus librarian). And lastly, I tried an alpha sort of the associated table and that did not accomplish my goal of sorting the journal title fields alphabetically in the form.

[ssanfu]

The question now is: how do I create an alpha sort predicated on the form's title field?

Would need to see your current dB..... not enough info (for me) to give a good answer.

[snowboarder234]

Would need to see your current dB..... not enough info (for me) to give a good answer.

DB as per your request. See frmPeriodicals. I want to create a alpha sort by the Title field. Right now the titles are not in alphabetical order.

[ssanfu]

If I understood correctly:

Open the form "frmPeriodicals" in design view.
Open the form properties dialog box.
Click on the DATA tab.
In the "Row Source" property, click on the ellipsis (...) to edit the SQL string
Find the column "JournalTitle" (7th column) in the grid.
In the "SORT" row, select "Ascending"
Close the query builder, Save the changes.
Close the form, Save the changes.







The records should be sorted by the Title.

[snowboarder234]

Thank you for your assistance. Before I tackle the alpha sorting issue in which you supplied the solution, I have another, more pressing concern. I came in this morning and the data I entered into this periodical/journal form was gone. No other data in the other tables/forms in the database is missing. I backed up the file before I left work and that file also is missing the data in the periodicals table and its associated form. That category only has 26 records, so it's not devastating -- just annoying. My question is, how does this happen and how do I prevent it from occurring again in say, the books table which now has over 500 records? This problem was specific to the form, not the subform.

If I understood correctly:

Open the form "frmPeriodicals" in design view.
Open the form properties dialog box.
Click on the DATA tab.
In the "Row Source" property, click on the ellipsis (...) to edit the SQL string
Find the column "JournalTitle" (7th column) in the grid.
In the "SORT" row, select "Ascending"
Close the query builder, Save the changes.
Close the form, Save the changes.







The records should be sorted by the Title.