Question Custom Notes on Orders

[fastfreddie]

I'm looking to simplify some aspects of our company's order database, including standard notes that appear on the sales orders that we send to customers. We have different lines of business and the notes vary. We currently have several reports, depending on the type of order, with the notes hard coded in labels. It's not an elegant, nor scalable, solution.

Our ideal solution: One order report only, with an option for the user to select one or more notes from a list when they enter the order. I'm thinking a popup form with a checkbox next to each note, where each checked note will print on the order report.

Is the above scenario possible?
-If yes, any suggestions for how to best accomplish this would be appreciated. I assume the first step would be to create a table for the notes, but I'm not sure where to go beyond that.

If the proposed solution is not workable, or if there are better ways to accomplish the same task, I welcome all suggestions.

 

[plog]

Sounds like you need 2 tables. One for all of the notes, then another one to assign notes to Orders. The structure would look like this:

Notes
Note_ID, Note_Text
1, "This note will appear on the order."
2, "This is the second note that will appear on the order."
3, "Yet another note to appear."

OrderNotes
ID_Order, ID_Note
21, 1
21, 3
22, 1
24, 2
24, 3
28, 1
28, 2
28, 3

With that you can assign as many notes as needed to an order. To display the notes on a report would require a sub-report and assigning notes to an order would need a sub-form.

 

[CJ_London]

I suggest your notes table would have the following fields

tblNotes
NotesPK autonumber
NoteDescription text (to describe the note)
Note memo (could be text, depends how many characters you require, max for text is 255)

You may need an additional field or two if notes need to appear in specific places and/or in a specific order


Then you would have a linking table

tblLinkNotes
LinkPK autonumber
OrderFK long - links back to order
NoteFK long - links back to the note

the additional fields mention able could appear in this table instead - it all depends on your requirements

Assuming notes just appear in a subreport you would have a subreport recordsource of

 SELECT OrderFK, Note 
 FROM tblNotes INNER JOIN tblLinkNotes ON tblNotes.NotePK=tblLinkNotes.NoteFK

and you would set the subform linkchild property to OrderFK and the linkMaster to your OrderPK (or its equivalent

 

[CJ_London]

@Plog - Snap!

 

[plog]

I might have been first, but yours is more thorough.

fastfreddie: Follow CJ's method.

 

[fastfreddie]

Wow, thank you both plog and CJ for such quick replies. This was my first post here and the turnaround was even quicker than I had hoped for! It sounds like both of you have the same general idea, so I'm going to set up the two tables that way and take it as far as I can from there.