Issue with one to many relationship

[ryancgarrett]

Hi guys,



I am a super noob when it comes to Access, but I'm pretty handy with Excel and VBA programming and my wife just started a business that could use a database to store client information and so on so I'm giving it a go. I'm having an issue already though with building relationships. Quick background about the business: It's a wedding and event floral company; the process is, a customer signs up, receives a quote for an event, each event has multiple flower arrangements and each arrangement has multiple flowers in it. That being said, I have the following tables:

tblCustomer
tblQuote
tblEvent
tblArrangement
tblFlowers

I have created relationships between the tables as I have read about and what seemed logical and everything worked except for the relationship between tblArrangement and tblFlowers. FlowerID is the primary key for tblFlowers and a secondary key in tblArrangement. As each arrangement will have multiple flowers, yet individual flowers can be in multiple arrangements I'm a bit stuck. Further, Access will only allow me to create a one-to-many relationship with tblArrangement as the related table, no other way. Am I missing something here or is there a better way to go about this?

Thanks in advance for the help I'm sure I'll need over the next few days, but hopefully it won't be long before I can answer a few questions myself on the forum!

Best,

Ryan

[June7]

Consider:

tblArrangements
ArrangementID (primary key)
ArrangementName
ArrangementPrice

tblFlowers
FlowerID (primary key)
FlowerName

tblArrangeFlowers
ID (primary key)
ArrangementID (foreign key)
FlowerID (foreign key)

tblQuote
QuoteID (primary key)
EventID (foreign key)

tblQuoteDetails
QuoteID (foreign key)
ArrangeFlowersID (foreign key)

[orange]

Further to June7's advice:

- do Quotes become Orders?
-need a table for Event
-you will/may need a Date field on Event, Quote..
-you may need CustomerId on Quote

If you include Price on the Arrangement, you should consider that the CurrentPrice. When you "sell" an arrangement(s) to a Customer, add a field to handle Quantity of Arrangements and AgreedUponPrice. This will allow you to change the Price of an Arrangement without modifying all your existing records. It also allows you to have Sales, Clearance Items, Loyalty Program, Discounts etc.

Good luck with your database project.