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; (25078.13, « »)
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.
http://ms-access.itags.org/q_ms-access-database_151364.html
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.itags.org.discussions.microsoft.com> wrote in message
news:D725AC44-54A5-4F9F-BEE5-8CBDFDC57A2C.ms-access.itags.org.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.itags.org.discussions.microsoft.com> wrote in message
> news:D725AC44-54A5-4F9F-BEE5-8CBDFDC57A2C.ms-access.itags.org.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.itags.org.discussions.microsoft.com> wrote in message
news:3160B7B2-FD75-412A-BE07-5B73EF50FC0B.ms-access.itags.org.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