Advice On Structure & Concept

[CharlesWhiteman]

I am designing part of my database to simply the production of predictable quotations. (and have been for the last 6 months). My quotations tend to be for one product whose price varies based on the number of software licences. In each quote there will be the price for the software and the price for installation.

I will have a TblItems. In that table will be all of the items available for quotations and may include, for example, the following

Group (5 User system) Item, Description, Price, cost

In this group there will be two matching items

On my quote form i want to have a drop down field which will allow mw to simply select the number of users and then i want Access to take any item which belongs to the 'number of users selected' to another table and i will then include a quoteID etc etc.

Can anyone advise me on, the main thing, the kind of database concept on how to achieve this please?

 

[ajetrumpet]

Charles,
I would suggest the following....

I will have a TblItems...Group (5 User system) Item, Description, Price, cost

create fields called "system or item" and "numberofusers"

On my quote form i want to have a drop down field which will allow mw to simply select the number of users and then i want Access to take any item which belongs to the 'number of users selected' to another table

create a combo box on the form and in the wizard, select tblItems.numberofusers as the lookup field. You will probably have to change the row source to SELECT DISTINCT in the combo's properties if you have many systems to choose from and the same "number of users" option for many of the systems.

To get the records out of the Items table and displayed in a new table based on your "users" selection, put a command button on your form and attach it to the "run query" option specified in the wizard.

Before you do this though, make sure you set up either a make-table query or select query and type your SQL in....

Your code will probably look something like...

SELECT (*)
FROM tblItems
WHERE tblItems.numberofusers = [Forms]![quote form name]![combo box name];

Also, if you have a quotations table and you're going to want some of those fields included in your new table generated by this query, you will need to include a JOIN function in the query code as well.

 

[CharlesWhiteman]

Thanks for you advice. It is some times useful to have another perspective on a problem. I can see how your advice can work. I'll probably pop a copy of the Db up here when its finished.

 

[CharlesWhiteman]

Hi, I set up my Db in line with your instructions and it works fine. Great. The problem i am now not solving is that when the data is appended i also need to take the value of a textbox which is on the same form as the combo box to use as a refernece number binding that quote selection to the main quote itself. I've tried settign the default value on the table 'TblQuoteItems' using an expression to refer to the form but that didnt work. I also tried converting the query into Vb and i thought i could probably then include the txtbox value but I kept gettign systax errors. can you offer any advice please?

 

[ajetrumpet]

a refernece number binding that quote selection to the main quote itself

Charles,
It sounds like maybe you need to create a table that holds all of the quotes you make (per item) and assign reference numbers to each of them. Creating a new table like this will allow you to query reference numbers and fetch records which contain the specific quote reference (is this part of the outcome you are looking for?). It doesn't sound like the setup you have will compliment this process. You may have to restructure a bit.

I apologize if I led you in the wrong direction, doesn't sound like I did, but I'm not sure if all the facts were present here before I posted...

On my quote form i want to have a drop down field which will allow mw to simply select the number of users and then i want Access to take any item which belongs to the 'number of users selected' to another table and i will then include a quoteID etc etc.

I try to answer the specific questions that are asked unless the person also specifies a particular outcome that is needed...

I'm not sure I can visualize what you're asking, if you want to attach your database here, I could take a look...

If not, maybe someone in the "Modules and VBA" section of the forum could help you with some procedure code.

 

[CharlesWhiteman]

Thanks for taking the time to reply. Yes, I'll pop a copy up so you can take a look. I've already built most of it once, in fact I al already using a prototype. this is the next step The point of what i am doing with this part of the Db is to have a system which will automatically generate quotations, i have not put the corresponding reports in yet. With regard to the referencing; I have TblQuotes, this stores parent data. TblQuoteItems stores the line items relating to the quotation. The quotation will have a reference number, VB generated, and i anticipate that I will want to use this as a PK/FK.

 

[CharlesWhiteman]

The objects in question: when you open the Db, goto FrmDatabase, opportunities, new quote, this opens FrmQuoteBuilder. This relates to our posts. I will ben entering key data into the new quotation form and then to choose the line items goto quote builder. Choose, Cbutton and then i will have a list box on my quote form which will have a query based on the quote reference.

 

[ajetrumpet]

Charles, do you have the file in this thread??

Sorry it took so long to get back to you...I am without Microsoft office now for about 2 weeks. I am having to go to the library to use the Access program.

Something must have happened when you uploaded the file, I don't see it on this page. Did you zip it??