Is Access Capable of doing this? (1 Viewer)

Rich_B · Feb 18, 2016

Hi

I have been trying to convert to my Excel quotes, orders and invoicing system to Access so that multiple users can all input data simultaneously. However, I am running into problems that I am now not sure if Access can handle.

In my current Excel version, as quotes are being written formulas in the cells auto calculate the price of each item in the quote based on the dimensions (L, W & H) and the material used to manufacture the product. This all happens in real time as the data is entered and is an essential part of my system.

I have been trying to convert this into an Access form but not only are the calculations a bit laggy compared to Excel but I'm discovering that once the calculations are calculated in the Access form, the data cannot then be transferred to the table as the control source box is taken up by the expression making the calculation in the form.

All I need to do is to have an input form make calculations in real time, so that the user can see the calculations and make adjustments if necessary, and then put the result in the table.

Is this possible or do I need something other than Access?

Thank you

Ranman256 · Feb 18, 2016

The calculations are done in queries. You CAN do it in a form, but a query can also show on the form.
Use queries.

Mile-O · Feb 18, 2016

Ranman256 said:
The calculations are done in queries. You CAN do it in a form, but a query can also show on the form.
Use queries.

I told him as much yesterday.

http://www.access-programmers.co.uk/forums/showthread.php?t=284693

Rich_B · Feb 18, 2016

Ranman256 said:
The calculations are done in queries. You CAN do it in a form, but a query can also show on the form.
Use queries.

Thank you for your input.

So are you saying that by using this method the query will auto calculate the result within the form as soon as the data is entered i.e. as an Excel spreadsheet would, and then still transfer it to the table?

If so, where would I place the query?

Sorry, I'm a bit of an Access newbie.

Rich_B · Feb 18, 2016

Mile-O said:
I told him as much yesterday.

http://www.access-programmers.co.uk/forums/showthread.php?t=284693

Thank you for that. I hadn't noticed your final post there.

Mile-O · Feb 18, 2016

Rich_B said:
So are you saying that by using this method the query will auto calculate the result within the form as soon as the data is entered i.e. as an Excel spreadsheet would, and then still transfer it to the table?

You're in Access now; leave the Excel thinking at the door.

You can calculate in queries and make these queries your form's RecordSource, rather than a table. The control's ControlSource need only do the calculation for display purposes. You need never store the result of the calculation.

Rich_B · Feb 18, 2016

Mile-O said:
You're in Access now; leave the Excel thinking at the door.

You can calculate in queries and make these queries your form's RecordSource, rather than a table. The control's ControlSource need only do the calculation for display purposes. You need never store the result of the calculation.

Thank you for explaining this but I do need to store the results of the calculations.

The user inputs the LxWxH, material type and price per m3 of material. These five variables determine the price. The reason I need the calculations to be made in real time is so the user can adjust the m3 variable to arrive at the most suitable price for us to sell at. So the result of the calculation is the price. Once a suitable price has been settled on by my estimators this then becomes a quote which does need to be stored.

So what I am aiming for is an input page that allows the user to alter the variables multiple times which will calculate the price in real time based on underlying expressions. Then when all the data input is settled on, the final version needs to be stored as a record, which I assumed would be in the table of that form.

cyanidem · Feb 18, 2016

So what was the problem with doing your calculations in AfterUpdate or OnChange events of each L, W and H and putting result in Price textbox? You were asking about that yesterday AFAIR and...? That's how you want it to work, right?

Rich_B · Feb 18, 2016

cyanidem said:
So what was the problem with doing your calculations in AfterUpdate or OnChange events of each L, W and H and putting result in Price textbox? You were asking about that yesterday AFAIR and...? That's how you want it to work, right?

The problem of making the calculations in real time without having to refresh or save was solved. So now in my input form as the variables are all inputted the result calculates in real time as it should do which is great. The problem now is that once all this data has been inputted (say several rows worth) it needs to be saved as a quote and stored somewhere (which I assumed would be the table of that input form). This couldn't happen because the control source box was populated by the expression making the calculations. I was then advised to take the expression out of the control source box and put it in the default values box and create some code in the after event box which I did but it didn't seem to work.

Also, since then I have seen Mile-O's post regarding the way I was structuring everything being flawed anyway.

So I'm now trying to reassess if Access is even the right application for my needs or whether I simply need to structure my system differently to suit Access. Hopefully it is the latter.

Minty · Feb 18, 2016

Rich_B said:
So I'm now trying to reassess if Access is even the right application for my needs or whether I simply need to structure my system differently to suit Access. Hopefully it is the latter.

You are beginning to see that Access is Not Excel. You are heading in the right direction, but have to make that leap of faith that stops you thinking in Excel methods and starts thinking in Data methods.

I didn't see your structure from the other thread, but it sounds as if you have probably initially simply duplicated your spreadsheet layout into a couple of tables.

As you have now discovered this may not be the way to use Access at its best. But it doesn't mean you shouldn't use Access, you just need to get the data stored in the correct way to then give you the results you are after.

If you continue to ask sensible questions and show your efforts you will get loads of help from the people here. But you will be on a slightly steep learning curve initially. My advice would be to persevere and keep learning. What you are trying to achieve is certainly doable.

Rich_B · Feb 18, 2016

You are beginning to see that Access is Not Excel. You are heading in the right direction, but have to make that leap of faith that stops you thinking in Excel methods and starts thinking in Data methods. - Thank you, I really want to understand Access as this is the direction I need to go in as my business has simply outgrown what Excel can offer. I am not intentionally clinging on to Excel thinking, I just keep referencing it to try and explain how I want Access to perform for my system.

I didn't see your structure from the other thread, but it sounds as if you have probably initially simply duplicated your spreadsheet layout into a couple of tables. - Pretty much yes, but I have created various tables and forms to suit the various fields required.

As you have now discovered this may not be the way to use Access at its best. But it doesn't mean you shouldn't use Access, you just need to get the data stored in the correct way to then give you the results you are after. - This is what I thought, still not quite there yet though.

If you continue to ask sensible questions and show your efforts you will get loads of help from the people here. But you will be on a slightly steep learning curve initially. My advice would be to persevere and keep learning. What you are trying to achieve is certainly doable. - Thank you for the encouragement. This forum is certainly helping a lot so far. I'm glad to hear that Access can do what I'm trying to do as I don't fancy taking on something like SQL Server.

Mile-O · Feb 18, 2016

Rich_B said:
The user inputs the LxWxH, material type and price per m3 of material. These five variables determine the price. The reason I need the calculations to be made in real time is so the user can adjust the m3 variable to arrive at the most suitable price for us to sell at. So the result of the calculation is the price. Once a suitable price has been settled on by my estimators this then becomes a quote which does need to be stored.

A table structure, something basic like this, should do exactly what you are wanting to do without having to store calculated values in your table.

tblMaterialTypes
MaterialTypeID (Autonumber, primary key)
MaterialType (Text)

tblMaterials
MaterialID (Autonumber, primary key)
MaterialName (Text)
MaterialTypeID (Number, foreign key)

tblMaterialPrices
MaterialPriceID (Autonumber, primary key)
MaterialID (Number, foreign key)
StartDate (Date)
EndDate (Date)
Price (Currency)

tblOrders
OrderID (Autonumber, primary key)
MaterialPricebnID
Length (Number)
Width (Number)
Height (Number)

gemma-the-husky · Feb 18, 2016

in general, access can do anything, but designing access apps is not trivial.

designing a good database is an order of magnitude higher than designing a good spreadsheet. You can use excel without ever writing a macro (code). You won't get very far in access without needing a lot of code.

In fact Access is just so powerful, a lot of the time we are writing code to restrict what users can do.

The_Doc_Man · Feb 18, 2016

Concur with Dave, and add this comment:

When asking whether Access can do something, more often the problem is our insight, our imagination, and the amount of time we can spend on something rather than whether it is possible to do it in Access. Don't get me wrong, Access has it limits - but they are remarkably generous in the amount of "wiggle room" you have to make things happen.

The bigger problem is that Access offers you SO many options that you have a free choice to do something the wrong way and still make it work if you don't object to tearing out your hair now and then. (I think that is what happened with my own hair...)

Is Access Capable of doing this? (1 Viewer)

Rich_B

Access Newbie

Ranman256

Well-known member

Mile-O

Back once again...

Rich_B

Access Newbie

Rich_B

Access Newbie

Mile-O

Back once again...

Rich_B

Access Newbie

cyanidem

Nieóhfytny jaszczomp

Rich_B

Access Newbie

Minty

AWF VIP

Rich_B

Access Newbie

Mile-O

Back once again...

gemma-the-husky

Super Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread