If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
1 quote asociated to multiple customers?
Help! I'm stumped on this one and don't know how to proceed. I've been given the task of changing our customer database so that I can issue a quote to multiple customers. Without having to re-enter the quote information multiple times, how can I select multiple customers, type in the quote, and have that quote associated so that when I pull up any of the customers that quote will be associated? Any help you can give me is GREATLY appreciated.....
|
#2
|
|||
|
|||
1 quote asociated to multiple customers?
Looks like a classic many-to-many relation, i.e.:
- one customer can have many quotes over time, and also - one quote can have many associated customers. If so, you need to create a junction table. It will have fields: - QuoteID foreign key to QuoteID - CustomerID foreign key to CustomerID. This table gets one record for each combination of QuoteID + CustomerID. (The Quote table has no CustomerID field, and the Customer table has no QuoteID field.) Now you need a subform on your Quote form to the associated customers. The subform will contain a combo box where you can select the customer associated with the quote. Add as many customers as needed, one per row. For another example of a junction table, see: http://allenbrowne.com/casu-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "debraj007" wrote in message news Help! I'm stumped on this one and don't know how to proceed. I've been given the task of changing our customer database so that I can issue a quote to multiple customers. Without having to re-enter the quote information multiple times, how can I select multiple customers, type in the quote, and have that quote associated so that when I pull up any of the customers that quote will be associated? Any help you can give me is GREATLY appreciated..... |
#3
|
|||
|
|||
1 quote asociated to multiple customers?
Thanks! I'll give it a try right away!
----- Allen Browne wrote: ----- Looks like a classic many-to-many relation, i.e.: - one customer can have many quotes over time, and also - one quote can have many associated customers. If so, you need to create a junction table. It will have fields: - QuoteID foreign key to QuoteID - CustomerID foreign key to CustomerID. This table gets one record for each combination of QuoteID + CustomerID. (The Quote table has no CustomerID field, and the Customer table has no QuoteID field.) Now you need a subform on your Quote form to the associated customers. The subform will contain a combo box where you can select the customer associated with the quote. Add as many customers as needed, one per row. For another example of a junction table, see: http://allenbrowne.com/casu-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "debraj007" wrote in message news Help! I'm stumped on this one and don't know how to proceed. I've been given the task of changing our customer database so that I can issue a quote to multiple customers. Without having to re-enter the quote information multiple times, how can I select multiple customers, type in the quote, and have that quote associated so that when I pull up any of the customers that quote will be associated? Any help you can give me is GREATLY appreciated..... |
#4
|
|||
|
|||
1 quote asociated to multiple customers?
Okay, maybe I confused myself when I tried this. Here are my tables
Quote (QuoteID, QuoteDate, etc.) Bidder (BidderID, BidderCompany, BidderContact, etc) LinkTable (QuoteID, BidderID) I created a main form with Job Info, tried to put in a subform with Bidder info, but it doesn't display the information I have in the table. What am I doing wrong? |
#5
|
|||
|
|||
1 quote asociated to multiple customers?
Job? Bidder in subform?
Try a main form bound to the Quote table, with a subform bound to the LinkTable. In the subform Access will automatically assign the QuoteID from the main form, and you will select the appropriate Bidder from a combo box. The combo will get its records from the Bidder table. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "debraj007" wrote in message ... Okay, maybe I confused myself when I tried this. Here are my tables Quote (QuoteID, QuoteDate, etc.) Bidder (BidderID, BidderCompany, BidderContact, etc) LinkTable (QuoteID, BidderID) I created a main form with Job Info, tried to put in a subform with Bidder info, but it doesn't display the information I have in the table. What am I doing wrong? |
#6
|
|||
|
|||
1 quote asociated to multiple customers?
If nobody has told you this today, You're Brilliant! That worked perfectly. Just one more question then I will leave you alone for today :-). From my quote form I would like to press a button to print out a quote (the particular quote I've selected) and I would like 1 quote (each on a new page) to print for each customer I selected in the drop down box, with each customer company printing in the TO: field. How do I make this work?
|
#7
|
|||
|
|||
1 quote asociated to multiple customers?
Hi Debra.
Before you go further, check if you need a QuoteDetail table as well as as your Quote table, so that one quote can contain many details (line items). For an example of how that works, see the Orders and Order Details tables in the Northwind sample database. To print a copy of the quote for each bidder: 1. Create a query that contains all 3 tables, and drag the fields you need for the report into the query design grid. Save. 2. Create a report based on this query. 3. In report design view, increase the height of the detail section so you can fit all your fields with the layout you want. 4. Open the Sorting And Grouping Box (View menu). Select BidderID, and choose Yes for the Group Header (lower pane of Sortin'n'Grouin' dialog). Access adds the section to the report. 5. Right-click the BidderID Header (grey bar), and choose Properties. On the Format tab of the Properties box, beside the Force New Page property, choose Before Section. This gives you a new page for each one. 6. Save the report with a name such as "rptQuote". That should give you a report that prints a page for each bidder. Now for the button to print out these quotes: 1. On the form, add a command button with these properties: Name cmdPrint On Click [Event Procedure] 2. Click the Build button (...) beside the On Click property. Access opens the code window. 3. Between the "Private Sub ..." and "End Sub" lines, enter: Dim strWhere As String If Me.Dirty Then 'Save record Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print. MsgBox "Select a quote" Else strWhere = "QuoteID = " & Me.QuoteID DoCmd.OpenReport "rptQuote", acViewPreview, , strWhere End If Enjoy. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "debraj007" wrote in message ... If nobody has told you this today, You're Brilliant! That worked perfectly. Just one more question then I will leave you alone for today :-). From my quote form I would like to press a button to print out a quote (the particular quote I've selected) and I would like 1 quote (each on a new page) to print for each customer I selected in the drop down box, with each customer company printing in the TO: field. How do I make this work? |
Thread Tools | |
Display Modes | |
|
|