Estimate form with more than one entry (1 Viewer)

[jillnoble]

Good morning - I have been tasked to add an estimate system to our current Access database using autonumbering for each new estimate.
I have very limited knowledge of Access and have basically taught myself (not that well!) to set up various forms/reports etc and this is my latest task.
I have managed to set up tables, a form and reports and it works quite well for what we need. However, the estimator has come to me with a problem which is above my remit i think...

My form only allows 1 cost per estimate, however, there are instances when multiple items need to be entered. We have got round this so far by exporting to a rich text Word file and amending manually, however, this isn't really ideal as if you search for the estimate on the DB it only brings up the original.

Is there anyway I could adapt what I already have to enter multiple lines on one estimate number? Not sure if it's something I should be using a sub form for but I've never used these so am a bit unsure.

Please see attached a copy of the DB and an example of what the estimate would ideally look like.

If this is a big change then we'll carry on as we are but if it is possible it would make more sense to do it properly.

As ever, any help greatly appreciated.

Thank you

 

[June7]

Need a related dependent table to hold each estimate cost items. And use a subform.

 

[arnelgp]

added "Estimate Details" table and create subform for it.
open "New Estimate" form to see.

you need to revise your report to accommodate the changes i made

 

[jillnoble]

added "Estimate Details" table and create subform for it.
open "New Estimate" form to see.

you need to revise your report to accommodate the changes i made

Thank you!! but I may not be using it correctly? When I try the print preview or report it's asking for parameters against each description? I also need to be able to change the cost for each individual item and list them separately as per the word doc example. Sorry, I maybe missing something or not explaining myself very well! I really appreciate you trying to help.

 

[Cotswold]

I wouldn't use Autonumbering for quote numbers.
Use a number in your masterfile that you read, add a new quote with that number.
Then advance the quote number in the masterfile.
If you have any corruption you may well end up with orphaned records containing detail.
You cannot replace a lost Autonumber but you can add a record and retrieve the orphaned records.
In the event, all you need is a utility to locate the missing number to replace in your parent table.

 

[gemma-the-husky]

Seriously, users shouldn't be telling programmers how to do their job, at least not at the point of "requiring" an autonumber. An autonumber cannot guarantee an intact sequence. Even if you could, occasionally autonumbers "jump" to a new seedvalue. There must be some reason it happens, but it does happen. Therefore if you include an autonumber, it will just guarantee a unique number for each record, which is often sufficient.

 

[jillnoble]

Seriously, users shouldn't be telling programmers how to do their job, at least not at the point of "requiring" an autonumber. An autonumber cannot guarantee an intact sequence. Even if you could, occasionally autonumbers "jump" to a new seedvalue. There must be some reason it happens, but it does happen. Therefore if you include an autonumber, it will just guarantee a unique number for each record, which is often sufficient.

Thank you for the reassurance, I was concerned I'd done something else wrong!

 

[gemma-the-husky]

Thank you for the reassurance, I was concerned I'd done something else wrong!

That was really just an observation. If you have an estimate table to store things like metadata for a "job", such as the estimate ID, the client, the project name and reference, the date started and so on, then that only gets you so far. If you have multiple items per estimate you need a subsidiary table linking the items to the estimate. If the estimate changes over time, you might need to store multiple versions of the estimate header.

So you need to consider how you want the estimating system to work. Ideally you would keep all the data in the database, rather than in spreadsheets and word files, but that all depends on the overall system design.

Depending on your organisation this could be a relatively trivial application, but it may equally be an important mission critical application, which very likely would affect the design, or at least inform the design.

 

[arnelgp]

here is a modification.

 

[jillnoble]

here is a modification.

Oh my god, that's brilliant!! I can work with that, thank you so much.

 

[Pat Hartman]

Quotes tend to be related to something. A job or a project for example. So, usually there is some "higher level" entity. You have a Customer table, a Job table, a Quote table, and an items table.

Jobs are related to customers. quotes are related to jobs and items are related to quotes. In most business models, you will produce one or more quotes for the same Job. So, rather than using an autonumber to sequence the quotes, you would use a user generated sequence that counts quotes within the same job. You would still use an Autonumber as the PK to the quote record but you would also have a FK to the Job and a sequentially generated seq number. The Job FK + the seq num make a two column unique index so that any job can only have ONE quote #2. So JobABC, has quote1, quote2, and quote3. Each quote may have the same or different items depending on what you are being asked to vary with your subsequent quotes.

Once you have a better grip on the business model, you will be better able to design proper schemas and not make rookie mistakes like not making the items 1-m with a quote. It wouldn't hurt to spend a day or two as a trainee estimator to understand how quoting actually works.

 

[jillnoble]

Quotes tend to be related to something. A job or a project for example. So, usually there is some "higher level" entity. You have a Customer table, a Job table, a Quote table, and an items table.

Jobs are related to customers. quotes are related to jobs and items are related to quotes. In most business models, you will produce one or more quotes for the same Job. So, rather than using an autonumber to sequence the quotes, you would use a user generated sequence that counts quotes within the same job. You would still use an Autonumber as the PK to the quote record but you would also have a FK to the Job and a sequentially generated seq number. The Job FK + the seq num make a two column unique index so that any job can only have ONE quote #2. So JobABC, has quote1, quote2, and quote3. Each quote may have the same or different items depending on what you are being asked to vary with your subsequent quotes.

Once you have a better grip on the business model, you will be better able to design proper schemas and not make rookie mistakes like not making the items 1-m with a quote. It wouldn't hurt to spend a day or two as a trainee estimator to understand how quoting actually works.

Thank you, I appreciate you spending the time to reply. However, this has been taken from an old database system the estimators have been using for years. I set up the Access one when their permissions stopped working, to work the same way , look the same etc and to their exact requirements, it was just getting 1+ lines on one quote I was struggling with but this has been solved by 'arnelgp' above but I appreciate the input.

 

[jillnoble]

here is a modification.

Thank you again for this. Like I said, I can work with it and it does exactly what I want, except... Is there any way I can get the detail in the report footer to appear directly after the details, no matter how many items are added? I've tried everything I can think of with my very limited knowledge/searching online... I've placed the Page header in the Report header on the attached, as I don't want the customer address appearing on each page and I can live with the Logo etc only being on the first page too, its just the big gap I've got after the items in the details section which doesn't look right. If its not possible, please don't worry but thought I'd ask in case it's something I'm doing wrong. Thanks again

 

[Gasman]

Use a Group footer and not report footer

 

[mike60smart]

Thank you again for this. Like I said, I can work with it and it does exactly what I want, except... Is there any way I can get the detail in the report footer to appear directly after the details, no matter how many items are added? I've tried everything I can think of with my very limited knowledge/searching online... I've placed the Page header in the Report header on the attached, as I don't want the customer address appearing on each page and I can live with the Logo etc only being on the first page too, its just the big gap I've got after the items in the details section which doesn't look right. If its not possible, please don't worry but thought I'd ask in case it's something I'm doing wrong. Thanks again

Hi Jill
Your problems are down to bad design.

The Estimate has many details so you need to structure the Report so that the Main Estimate details are contained in the Report Header
with all of the Details in the detail area.