New Estimate database

[Pintglass]

Hello
This is my first post so i hope its in the right place, sorry if its not.
I am also new to Access so i hope you can uderstand this.

I will try to explain what i am trying to do.

I am trying to create a database to produce estimate's for my customers, at the moment i have just one table called MATERIALS in this table i have just three fields for now:

ID
Item Description
Item Price.

I have created a form to view my records , on which at the moment i have just an
Item Description text box control
Item Price text box control
Quantity text box control unbound
Total Cost text box control unbound

What i am trying to do is enter the amount required into the quantity control and have the total cost update when this amount is entered.
I can get the total cost control to show the total by using an expression in the total cost control source but only when i place the cursor in the total cost control does the value appear.

I have tried various ways using events on the quantity control & on the total cost control but nothing seems to work.
Do i need to use vba for this or is there an otherway?

I know in the future i will have to have more controls on my form to make this useful any tips on that would be great, but for now i just wanted this simple form to practice on.

Thanks Alot Pintglass

 

[PaulO]

That's an interesting project you have there.

As a starting point I think you need several tables to form the foundation for a form designed to enter and manage unlimited Estimates:-

1) Estimate Headers with fields Estimate Number; Customer Code; Created Date; Completion Required Date; Completed Date; Status
2) Estimate Details with fields Estimate Number; Cost Type; Cost Code; Qty
3) Customers with fields Customer Code; Customer Name; Address Details etc
4) Cost Types with fields Code; Description (e.g. 01=Materials; 02= Labour etc)
5) Cost Codes with fields Reference, Description (this is where you'd hold all your different Materials)
6) Estimate Status with field Status (Open; Closed)

 

[Pintglass]

Thanks for the reply PaulO

I was hoping to make it a bit simpler than what your suggesting.

Basically I run a small building firm so all I want to do is have a database of all the materials that I use which I would create a table for.
From this table I would be able to search for a product by name, then from the results of the search be able to add this to a new list where I could add the quantity required.
Then when I have a list of items required for the job either save this as a report with just a total to print out to give to the client.
I have managed to get a list box showing the results of a search but after that I don't know how to add that to a separate list.
Any ideas would to a big help.
Thanks again Pintglass

 

[PaulO]

Thanks for the reply PaulO

I was hoping to make it a bit simpler than what your suggesting.

Basically I run a small building firm so all I want to do is have a database of all the materials that I use which I would create a table for.
From this table I would be able to search for a product by name, then from the results of the search be able to add this to a new list where I could add the quantity required.
Then when I have a list of items required for the job either save this as a report with just a total to print out to give to the client.
I have managed to get a list box showing the results of a search but after that I don't know how to add that to a separate list.
Any ideas would to a big help.
Thanks again Pintglass

Hi, I think in this reply you actually concede the need for additional tables e.g. "New List" = Estimates; "Client" = Clients

I can see what you're trying to achieve but I think you need something above simple but less than super-complex i.e. you need the capability to build up Client-specific quotes from scratch.

My approach will let you store all Quotes, track them, and use them as the basis for new quotes, etc.

 

[Pintglass]

I was hoping to add to the database as i go, could you tell me how i could add items from the search list to a list box to create a report on.

Thanks Pintglass

 

[Pintglass]

Hello PaulO

I have been referring to your original post, so ignore my post above.

Could you elaborate on the relationships for the tables you are suggesting.

Thanks Pintglass

 

[PaulO]

1) Estimate Headers with fields Estimate Number; Customer Code; Created Date; Completion Required Date; Completed Date; Status
2) Estimate Details with fields Estimate Number; Cost Type; Cost Code; Qty
3) Customers with fields Customer Code; Customer Name; Address Details etc
4) Cost Types with fields Code; Description (e.g. 01=Materials; 02= Labour etc)
5) Cost Codes with fields Reference, Description (this is where you'd hold all your different Materials)
6) Estimate Status with field Status (Open; Closed)

I would see the Relationships as indicated in matching colours above.

 

[Pintglass]

PaulO

I have setup my tables as the attached file.
What is the relationship between the estimate details table and the materials table?
Do the others look ok.

Thanks Pintglass

 

[PaulO]

ItemRef to ItemRef

 

[Pintglass]

Would that need to be a many to many relationship.

Thanks Pintglass