Solved Need help regarding my databse. Seeking experts advice. (1 Viewer)

[wikihow]

Hello MajP,

I have made some slow progress with the Database. I need some expert advice regarding some issues. In this DB, i need to store historic values i.e. store all quotations with datestamp and other details. What i am trying is to Append the required data in new transaction table which will not be directly editable by users and will act like the main source of quotations created till date.

Current working procedure of DB is: - create a client > create project related to client > Add a quote and 1 or more estimates to project > add units, unit types, rooms, and items to project according to quote/estimate.

A project will typically have one quote, but can have many revisions done by time (all revisions needs to be stored in a table as we may need to view any past quote anytime later)

A Quote can have one or more estimate depending the items type. one estimate can have wardrobes with sliding doors, other estimate in same quote can be of wardrobe with hinged doors.

Currently the progress is only upto unit and item schedule details and further I will attach more tables linking with products containing item detail, price, and more.


The issues i am facing is as below:

1. I have linked the quote and estimates table with projects. I am not sure if the link is correct. I need to show the quote ID and Estimate ID in frm_03 possibly in combo box, from where i can use selected values to save alongwith the above mentioned append query. I am having trouble having those selectable values of Quote ID and Estimate ID, if I link combo boxes with main form source. I have used individual source for combo boxes for now.

2. I am wondering if it would be better to not link quote and estimate table to project and we can somehow link it in any other way to the appended transaction table (historic values table) but, i still need the quoteID and EstimateID to appear in frm_03, as a user choice to select which quote or estimate we are saving.

3. Currently this is only upto unit and item schedule details and further we will attach more tables with products containing item detail, price, and more.

Please review and let me know if this can be done in a better way. As i am just learning, there may be many issues or errors or wrong methods in the DB.

Thank you.

 

[MajP]

I will take a look, but it may be a little while. Try to look this weekend.

 

[wikihow]

Sure, Take your time

I will wait for your response.
Thank you

 

[MajP]

Can you explain the transaction table a little more and what historical data is being saved?

1. I have linked the quote and estimates table with projects. I am not sure if the link is correct.
I need to show the quote ID and Estimate ID in frm_03 possibly in combo box, from where i can use selected values to save alongwith the above mentioned append query. I am having trouble having those selectable values of Quote ID and Estimate ID, if I link combo boxes with main form source. I have used individual source for combo boxes for now.

-- I think the relation is correct. I interpret this to mean that you saved Quotes and their estimates for a project. You want to have a way to select one of the Estimates and append this to the transaction table or select a quote and add all of its estimates. (However, based on the relationsip I think you just save estimates and their parent quote data). Would you save a quote that does not have an estimate? I would simply put buttons on both forms and do a select into. (May only need a button on the estimate form). Then just click on the estimate or quote that you want.

If you index your fields correctly you can ensure that no dupe records are created.

 

[wikihow]

Transaction table is nothing but all the data we entered into system to create a quote. If you check the relationship, you will see how there is a series going. starting with client details, project detail, units and room config, then further products, and later will be adding product details, prices and more related info.

We have been using these tables for creating a base quote for a client. All the tables (Clients, projects, units, unit types, rooms, products) are related to generate a quote. I am trying to keep any data (which can change in future revisions) as an appended historic table.

For example, if a Project manager is changed and the next quote revision we will be sending out in his name, then we cannot change the name of person in base table as it will also change it for previous quotes. Hence any data which can be changed periodically, we need to store it separately as historic values i.e. transactions table. (Transaction is just a name, can have any name for table, but its just the purpose we need)

One other major field is price, which can change often in different revisions of quote. we want to be able to change price in upcoming quotes, while not disturbing the values in our previous quotes. There are several other fields which may change in revisions with time.

For a moment, i am thinking that instead of having one or two major transaction table with all data combined, will it be good if i keep append an identical copy of each base table and relate them as base relationship. Then we add save button on all data entry forms with an append macro, which will save the records in those two tables (base and back-end transaction). Base table will be directly used by users to edit values etc. from time to time, but the same version of appended table will not be directly edited by users. All the searches, previous quotes etc can be directed to these backend transaction tables. Which are actually exact copies of base tables, appended while saving main tables.

Does this sounds better, instead of having 1-2 big combined transaction tables? I think because in big tables every value will be repeated, for example project number will stay in each record containing products and all details.

This is all the purpose of doing it this way. I have just started so I don't know if there is a better way to deal with such situation.


In Frm_03 > Page 02, the combo boxes, Requery does not work on those or may be i am not doing it correctly. I noticed that refreshing those combo boxes works. I also need them to be blank after saving a quote or after adding a new quote/estimate to system.

These combo boxes are not related to main form and have their own query generated from base tables. They look for a new quote number saved with status "Tendering" and displays them as selection. When the quote is saved and finalized the Tendering status changes and one should not be able to see those same quote number in combo boxes.

Wondering if not relating combo boxes to main form is good idea? or it can be any better. Sorry for asking too much, just trying to figure out how to build base structure of database yet.

Thank you in advance and appreciate your help.

 

[MajP]

These may help

Private Sub cboEstimateSelect_Enter()
  Me.cboEstimateSelect.Requery
End Sub

Private Sub cboQuoteSelect_Enter()
  Dim strSql As String
  strSql = "Select * from qry_QuoteSelect Where ProjectNumber = '" & Me.ProjectNumber & "'"
  Me.cboQuoteSelect.RowSource = strSql
End Sub

I do not know how to do macros (nor am I interested in learning) so I cannot help. At the size and complexity of this db, I would reccommend you go vba only. Maybe other people will have different opinions. But they are so limited and impossible to debug IMO. In the finalize button you need to requery the combos like I do in the above code. I do not know how to do that from a macro.

One thing you really need to get rid of is the lookup fields in a table. You can Google why these are bad. You can build combos on forms or in queries, but not in tables. Also to bring in related information instead of a combo use a query bringing that field (only need a combo for selecting).

As for the archive, this is complicated without seeing exactly what data needs to get archived. I doubt saving duplicate tables would work well and be efficient. This could get real complicated if an estimate has added or deleted records. I think you would end up basically having to archive the entire db each time. I do not think it is a single table either it could be several and these table may not be as normalized as your current tables. The focus would be on archiving just the needed information each time an estimate is finalized. This would be hard for me to come up with a good strategy without knowing what can change, be added, be deleted from one estimate to another. My gut feel is that exporting an estimate as one big query may be the easiest approach.

In the short term until you figure this out, I would add a time stamp to each table for both date created and possibly date modified for each record. You can google strategies to do this, but I think you are going to need time stamps.

 

[wikihow]

Thank you for giving your time and the code above. I will try to work it out as per your advice. As you mentioned, I will try to run things with VBA instead of macros.

 

[MajP]

Good luck. I will be gone on vacation, for a couple weeks and may not answer questions for a while. The bad thing is that once a thread is followed exclusively by a responder others may not join in.

 

[wikihow]

That's fine. I might open a new temporary thread if I need to ask anything and will get back to this main thread when you are back.

Have a good time.

 

[wikihow]

Hello MajP, Hope you are doing good.
I need help in adding a pricing structure for the items in database.

Scenario is we have say approx 10 basic product category. For example Product 1, Product 2, .... up to Product 10. Then we have 7 variant of each Product and that makes it. Product 1A, Product 1B, Product 1C and so on. and this goes same with other 9 products. Lets say total is 10 x 7 = 70 total variants.

Now we need to price each Variant. Each variant will have its own pricing slab. For example Product 1A ( if it is <500mm then $100, if <1000mm then $150, if <1500mm then $200 .. can go up to 5000mm.

Each Variant have its own pricing slab. As above example had <500mm, <1000mm, <1500mm, <2000mm, Other may have <700mm, <1200mm, <1700mm.

Please note that these are pricing slabs, product may not have round off size. it can be 586mm, 2156mm and like so. if a product is 586mm then it will be charged as <1000mm slab, if a product is 2156 mm then it will be charged as the next slab ahead of 2156mm depending on the sizes on price slab or price milestone.

So it is like its own pricing chart for one variant and we have 70.

I have attached an excel screenshot. We used a big chart and each cell have a big formula with conditions. That works, but still Excel is slow since there is much to calculate.

But now to make this work in Access, I wanted to know what can be the best strategy. Would it be good if we do it with VBA? Can VBA be used to write big codes. if yes, can you provide me a small code, so that i can use it to replicate for all my items.

I think, i can do it with macros with If code, but I am not sure as you said, that VBA is better than macros. In macros, i would thinking to do it by if condition, where i would target a field to get its Part/variant ID and then redirect it to some other related macro with that ID. In that other macro, i can add prices depending upon the size values with if condition.
This seems pretty much as doing in Excel, but i am not sure if this is going to be best.

Or may be you can suggest a better method for this scenario. We have the item selection and can manually enter size (height x width) and then depending on the width price needs to be auto filled or calculated.

Please suggest as this may need professional experience rather than me doing excel like stuff.


Thanks in Advance