Change records in ms Access based on Excel spreasheet?

[Asle]

Hi All,

I am using MS Access 2007.

I have a database for Quoting system for a Printing Company , where the user generate Quotes and all confirmed Quotes turn into a job.

Akin to that, i have a table for JobQuote and PrintJob. the problem i have is, whenever the JobQuote turns out as a PrintJob, it must check for availability of Stock corresponding to the input entered in the PrintJOB fields. All Stock information are stored in Excel Spreasheet.

say for example, a particular JobQuote to be done, it needs 400 papers. i want to check in the Stock Sheet for availability of papers. if i have 1000 papers, then when i turn that JobQuote into a PrintJob, it should update the Remaining Paper Stock as 600.

At the same time, when i have inadequote stock, it should prompt the user when the Quote is turned into Job.

Could anyone tell me how to head on with this?
thanks and regards,
Asle

[nico5038]

Your problem is a traditional stock keeping issue.
Personally I "solve" this by having per stockitem (E.g. Blue A4 paper) a "LastCountedSto ckQuantity".
Besides this I record the delivered supplies and the accepted orders.
Before accepting a new order I count the LastCountedStoc kQuantity, subtract the accepted orders and add the supplies. This gives me the number available and that's compared with the Qty of the QuoteJob to allow for "upgrading" to PrintJob.

The real benefit is the fact that changing a Printjob quantity can be tested with the same mechanism and doesn't require stock ajustments.
Once a year (or as required) you can hold a stockcount with the calculated stockquantity as the "should be" value and correct the administrative quantity with the real quantity and thus check on "leakage" or administrative errors.

Getting the odea ?

Nic;o)

[Asle]

Your problem is a traditional stock keeping issue.
Personally I "solve" this by having per stockitem (E.g. Blue A4 paper) a "LastCountedSto ckQuantity".
Besides this I record the delivered supplies and the accepted orders.
Before accepting a new order I count the LastCountedStoc kQuantity, subtract the accepted orders and add the supplies. This gives me the number available and that's compared with the Qty of the QuoteJob to allow for "upgrading" to PrintJob.

The real benefit is the fact that changing a Printjob quantity can be tested with the same mechanism and doesn't require stock ajustments.
Once a year (or as required) you can hold a stockcount with the calculated stockquantity as the "should be" value and correct the administrative quantity with the real quantity and thus check on "leakage" or administrative errors.

Getting the odea ?

Nic;o)

Thanks for ur reply Nic...

I need to make stock adjustments often , so that when the stock runs out, i have 2 order more stock. when the stocks received from the supplier, i should enter in the Stock Spreadhseet. say, A4 paper is '0' and if i receive 500 papers, then Stock for A4 in excel sheet is 500.

the problem lying here is, i dunno how to check the excel sheet from ms access for the availability of stock...

as of now, i can enter the client info, Quote info and update the confirmed Quotes into Job..

i dunno how to check for availbility of stock in EXCEL sheet from Access... i know it could be done in VBA but is there any article available regarding this?


thanks n regards,
Asle

[nico5038]

When the excelsheet is in "data format" having fieldnames in row 1 and data in columns below, you can link the excelsheet as a table.
Just open a new table and select the "Link" option.
Next change on the Fileform the Filetype into MS Excel and navigate to your excel sheet.
Now you can access the data using queries.

Clear ?

Nic;o)