A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

1 quote asociated to multiple customers?



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2004, 12:16 AM
debraj007
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 03:12 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:06 PM
debraj007
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 04:01 PM
debraj007
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 04:33 PM
Allen Browne
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 07:11 PM
debraj007
external usenet poster
 
Posts: n/a
Default 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  
Old May 25th, 2004, 02:26 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:42 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.