Relationship Question, Quote DB

[etrain]

Have not been here in a while. Hope all is well in Access land. My company here is getting a bit restrictive on the internet access we get.

Anyway on to my problem/issue.

I have a dB I am creating for Account Executives to create quotes to be sent to clients. Everything was going well until I got the functionality request from users and from executive management to be able to track the changes to a quote, revisions we call them.

I have 2 tables:
Quotes: (One Side)
QuoteID, Primary Key


Quotes Details: (Many Side)
QuoteID + ProductID - Combined Primary Key, to prevent same ProductID being selected twice on the same order, (user increases qty, yes I know I could do it in code and may do so but it works for now.)

There is currently a one-to-many relationship here. Works fine as intended.

I want to add a revision number to the main Quote table. So now it would have 2 primary keys, QuoteID + RevisionID. When I do this I lose the 1:m relationship, it becomes "Indeterminate" as you might guess.

Would you recommend changing the Detail table to have a new primary field called "QuoteDetailID" (AutoNumber Primary Key). Add QuoteID as a Foreign Key and use code to prevent the same ProductID from being placed on the same Quote more than once?

In simpler terms I want the user to be able to add a quote, make changes to it, get prompted with a question of whether it is a revision or an oops I made a mistake update, if revision then a the current quote is untouched and a new record is inserted with a revision number. (The quote ID never changes just the revision number)

sorry for the length. thanks for any input.

etrain
Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[mikey69]

In general, it is good practice to use keys which do not have significance in your application. Therefore, I'd strongly recommend using an autonumber field.

Regarding the "Oops, I made a mistake" situation, don't know which version of Access you are using, but XP and I think 2000 have an "On Dirty" event, which is well suited to dealing with this situation. Put a message box in this event asking for confirmation of the change, then increment the version number. It works really well.

 

[etrain]

Thanks. I generally don't like having tables with multple primary keys either. Yeah 2000 has the OnDirty event as well.

Yeah I think what I might do is change the primary key in both Quote and QuoteDetails table to an autonumber non signfigant number. Then just add code to autonumber the QuoteID and VersionID.

Regarding custom autonumbering. I have used two methods before. One using the DLookUp function with variables to increment the number and the other using a function that uses a increments and then stores the most recent in a table. Both work but have there problems. DLookUp can be slower but the other I have had numbers not incrementing and the only thing I can figure is a slow network might have cuased it.

So do you happen to have a method you use to auto increment? Any examples of code would be nice.

thanks Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[vanGough]

Hi,

This is simple I hope. I have ben using ADO to extratc and add data to tables. I also have a quote app I am writing. The DB is Access. I have a quote header table and a quote item table.

How would I use ADO to extract the one to many (header to items) and present this all one one form. Must I use stored procedures to extract? How about adding?

Thanks!

 

[mikey69]

For custom auto-numbering you should VBE and DAO or ADO. I don't have code easily available to paste, but if you send me an E mail, I'll dig something out. My E mail address youmike@mweb.co.za