How to use VBA in case of complex calculations instead of queries

[Ramnik]

Hello everyone,
Till now i just know some query designing and some amount of VBA for making small event procedures.
I want to know how we can use VBA when our queries becomes complex to replace the queries and to extend the functionality.
I don't know what ado,dao, or how to use recordsets in a VBA function.

Please help.
Thanks

 

[Galaxiom]

Generally a query is the faster way if the job can be done in a query.

Complex expressions in a query can be easier to write and maintain by creating a function in VBA. The function can be called in a query.

The down side of this is that the database engine can only apply functions it knows about. A custom user defined function must go back to Access for calculation. This can be much slower, particularly if the user defined function is in the where clause or join.

 

[Ramnik]

Thanks . But can you take a look at attachment and suggest me how should i go for it???

thanks.....

 

[Simon_MT]

The real question is what is the total quantity of backorders that can not be supplied and then which orders that can not be fulfilled. Doing crosstab is fine but you soon need a a vey wide screen to see the information.

Personally, I would produce two reports Total Stock Qty, Total Order Qty and Total Back Order Qty. Then a report of those orders that can not be fulfilled. Prioritising the allocation of stock to one order is another issue but it depends what has greater importance the overall position so that you see the overall picture or individual orders.

I do get what you are trying to do.

Simon

 

[Ramnik]

Thanks for reply.

Actually I need to prioritize the orders . I can calculate easily by totaling the orders and getting the total order shortage.
And there is no such issue of seeing all the information at one screen.
I need to issue the required quantity order-wise (priority-wise) . single order at time . so for this i need priority-wise shortage of each order with high priority first.
I do not want best-fit fulfillment of orders.
Thanks

 

[Ramnik]

Can anyone help me to achieve the required query mentioned above ????

Thanks