Tags: access, create, created, database, ive, lease, maintenancefor, management, microsoft, mysql, oracle, order, purchase, repairs, retail, sql, stores, system, tracking

Purchase order tracking

On Database » Microsoft Access

9,369 words with 5 Comments; publish: Sun, 01 Jun 2008 11:53:00 GMT; (25093.75, « »)

I have been asked to create a PO tracking system for repairs and maintenance

for 116 retail stores. I've already created the Lease Management database

with all of the store information, and plan to link the store information to

the PO tracking.

This database is being created to track actual costs vs. budget for each

store. I've read other posts on this subject, but find that the Invoice from

Northwind doesn't really work for this purpose.

Here's my problem. This database will be separate from the company's

existing accounting system, and our department will be assigned blocks of PO

numbers as we need them. How can I use these numbers to create the PO's in

our database?

Any help would be greatly appreciated. I have been given one week to

complete this.

Thanks again.

All Comments

Leave a comment...

  • 5 Comments
    • I would also note that the PO numbers can only be used once. HELP?

      Debbie

      "D. M." wrote:

      > I have been asked to create a PO tracking system for repairs and maintenance

      > for 116 retail stores. I've already created the Lease Management database

      > with all of the store information, and plan to link the store information to

      > the PO tracking.

      > This database is being created to track actual costs vs. budget for each

      > store. I've read other posts on this subject, but find that the Invoice from

      > Northwind doesn't really work for this purpose.

      > Here's my problem. This database will be separate from the company's

      > existing accounting system, and our department will be assigned blocks of PO

      > numbers as we need them. How can I use these numbers to create the PO's in

      > our database?

      > Any help would be greatly appreciated. I have been given one week to

      > complete this.

      > Thanks again.

      #1; Sun, 01 Jun 2008 11:54:00 GMT
    • As we don't know the requirements of your P.O. tracking application, or how

      it will be used by multiple stores, etc., it's hard to say if a week is

      somewhat inadequate or grossly inadequate. But, given that it was

      "edicted", it's a good guess that it is inadequate.

      Does the person who gave you the deadline know anything about software /

      database development? I have seen "tracking applications" of various kinds

      in different languages, some of which took multiple people multiple months

      to develop.

      You can create a table of P.O. numbers in one of several forms, obtain the

      PO number and modify the data so it will not be available again. The

      simplest way, perhaps, would be to create a Form into which you can enter

      the beginning and ending numbers of the block, and write a separate record

      into the "Available PO Number" table. Then, when you use one, delete that

      record from the availability table. You could, alternatively, have a table

      with records indicating blocks of numbers, and update that -- my

      recommendation would be "easiest way" because disk storage is a lot cheaper

      than developer time.

      Larry Linson

      Microsoft Access MVP

      "D. M." <DM.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:D725AC44-54A5-4F9F-BEE5-8CBDFDC57A2C.ms-access.todaysummary.com.microsoft.com...[vbcol=seagreen]

      >I would also note that the PO numbers can only be used once. HELP?

      > Debbie

      > "D. M." wrote:

      #2; Sun, 01 Jun 2008 11:55:00 GMT
    • Larry,

      I'm sorry that I wasn't clearer. Basically, our process now is: We hire

      someone to make repairs or do maintenance in a store and we prepare a PO.

      The work is done and the invoice is sent to Accounting. There is NO process

      in place for tracking costs.

      Because the company is in the middle of a restructure, changing the current

      processes will have to wait. Until that time we want to create a very simple

      database that stores the PO and invoice information. We can then track the

      amounts charged to each store. I will create reports showing what work was

      done when and by whom by store, by vendor, etc. To clarify, this will only

      track the PO's created by our department.

      Hope that clears it up a bit.

      I have already created the table with the PO Numbers. From there, I don't

      understand how the 2 forms work together. Can you please clarify that for me?

      Thanks so much for your time.

      "Larry Linson" wrote:

      > As we don't know the requirements of your P.O. tracking application, or how

      > it will be used by multiple stores, etc., it's hard to say if a week is

      > somewhat inadequate or grossly inadequate. But, given that it was

      > "edicted", it's a good guess that it is inadequate.

      > Does the person who gave you the deadline know anything about software /

      > database development? I have seen "tracking applications" of various kinds

      > in different languages, some of which took multiple people multiple months

      > to develop.

      > You can create a table of P.O. numbers in one of several forms, obtain the

      > PO number and modify the data so it will not be available again. The

      > simplest way, perhaps, would be to create a Form into which you can enter

      > the beginning and ending numbers of the block, and write a separate record

      > into the "Available PO Number" table. Then, when you use one, delete that

      > record from the availability table. You could, alternatively, have a table

      > with records indicating blocks of numbers, and update that -- my

      > recommendation would be "easiest way" because disk storage is a lot cheaper

      > than developer time.

      > Larry Linson

      > Microsoft Access MVP

      >

      > "D. M." <DM.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:D725AC44-54A5-4F9F-BEE5-8CBDFDC57A2C.ms-access.todaysummary.com.microsoft.com...

      >

      >

      #3; Sun, 01 Jun 2008 11:56:00 GMT
    • When you create a new PO, you use code to retrieve the "next" PO number,

      apply it to the PO, and then delete it from the table of POs. I presume you

      are using a Form to enter the PO (there are too many things that can go

      wrong using datasheet view).

      This code, executed but not thoroughly tested, in the BeforeUpdate event of

      the Form, appears to work:

      Dim db As DAO.Database

      Dim rs As DAO.Recordset

      Set db = CurrentDb

      Set rs = db.OpenRecordset("qryNextPO")

      If Not (rs.BOF And rs.EOF) Then

      rs.MoveFirst

      Me!txtPONo = rs!PONo

      rs.Delete

      End If

      rs.Close

      Set rs = Nothing

      Set db = Nothing

      qryNextPO retrieves the lowest number from tblPOSource, with TopValues in

      the Query properties set to 1. It saves that value into the Control txtPONo

      on the Form, then Deletes the record from the tblPOSource. Writing the

      actual PO is handled automatically by Access when you move off the current

      Record or Close the Form.

      If your application may concurrently be used by more than one user, you can

      precede the read/write statements in this code with an Access "BeginTtans"

      and use corresponding "Commit" and "Rollback" -- as explained in Help. If

      only one person at a time will be using it, there's no need to force all the

      read/write to be done as a unit.

      Larry Linson

      Microsoft Access MVP

      "D. M." <DM.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:3160B7B2-FD75-412A-BE07-5B73EF50FC0B.ms-access.todaysummary.com.microsoft.com...[vbcol=seagreen]

      > Larry,

      > I'm sorry that I wasn't clearer. Basically, our process now is: We hire

      > someone to make repairs or do maintenance in a store and we prepare a PO.

      > The work is done and the invoice is sent to Accounting. There is NO

      > process

      > in place for tracking costs.

      > Because the company is in the middle of a restructure, changing the

      > current

      > processes will have to wait. Until that time we want to create a very

      > simple

      > database that stores the PO and invoice information. We can then track

      > the

      > amounts charged to each store. I will create reports showing what work

      > was

      > done when and by whom by store, by vendor, etc. To clarify, this will

      > only

      > track the PO's created by our department.

      > Hope that clears it up a bit.

      > I have already created the table with the PO Numbers. From there, I don't

      > understand how the 2 forms work together. Can you please clarify that for

      > me?

      > Thanks so much for your time.

      > "Larry Linson" wrote:

      #4; Sun, 01 Jun 2008 11:57:00 GMT
    • Are the PO's sequential within a block? If so, could you create a simple

      table named PONums containing one field: PONum which you seed with the

      beginning block number, then when you need a new PO you open the table, read

      the number, increment the number, close the table.

      >...we want to create a very simple

      > database that stores the PO and invoice information. We can then track

      > the

      > amounts charged to each store. I will create reports showing what work

      > was

      > done when and by whom by store, by vendor, etc.

      Isn't this a simple data entry situation? Someone enters the PO and invoice

      information into a table and then you run Totals queries to total the

      numbers grouped by Store and generate corresponding reports?

      #5; Sun, 01 Jun 2008 11:58:00 GMT