One Article, Two Categories

[lph2005]

Two database tables: notes and categories

Both notes and categories have the field category_id. Categories table category_id is set to Autonumber. Notes category_id is set to number. Therefore, only one number is allowed in that field.

How can I get it so that a note may have multiple categories?

------

UPDATE:

Decided to add a third table notes_categories with note_id, category_id ... this would hold multiple rows of same note_id but different categories. However, I have NO idea how to get these to update in a submission form. The submission (DataPage) is based on the table "notes" and maybe it should be based on something else. If I use a view then the submission forms says no table is editable.

[MayMusic]

So each note can have multiple category and each category can have multiple notes!!!Right? This is called many to many relationship which should be braked to one to many (normalized).

Let's say you have this table:

Note: Note_ID (Autonumber), Note

Category:Category_ID (Autonumber), Category

You need to create a another table to show the relationship:

Note_Category: ID(Autonumber), Note_ID (Number), Category_ID (Number)

In your submission page then you need to a multistep page to submit your data to the tables, since each DataPage can only have one table source. First submission is based on Notes table you pass the ID over to the next page based on category and receive it in a hidden field, then from here pass both IDs to the third page based on "Note_Category" table. This page can be hidden and can have auto submit script to submit the relationship to the table.

To auto submit the page add this code to the footer:

function f_submit() {

document.getElementById("caspioform").submit();

}

window.onload = f_submit;

and to hide the DataPage add

to the header of the page and

to the footer.

[lph2005]

So each note can have multiple category and each category can have multiple notes!!!Right? This is called many to many relationship which should be braked to one to many (normalized).

Let's say you have this table:

Note: Note_ID (Autonumber), Note

Category:Category_ID (Autonumber), Category

You need to create a another table to show the relationship:

Note_Category: ID(Autonumber), Note_ID (Number), Category_ID (Number)

Thank you for the reply. I'm back to square one. I let the trial go because this was not working. I decided, though, to go for it - ordered the account - and jumpstart.

Currently, I can set an article to have many categories but a report will show the same article several times.

Tables are as stated in OP. DataPage report is set to show article title, article notes, and source URL based on search criteria of article notes, or category, or keyword. But the same article shows up multiple times because it has multiple keywords attached to it.

How can I get the report to only show the same article once?

How can I get these articles grouped so that the first one shows title and notes, the others just show the title?

Can I link to the work in this forum ? Is that considered bad form? Some forums do not want links to work ...

How can I show how the DataPage is built?