Need help with inventory system based on dates

[tomasro]

I am a fairly novice user of Access, but I'm learning a lot as I get more and more involved projects. But now I find myself really stuck and need some help:

The project I have before me is a database system for my rental company. It is a very small company, but the goal is to allow this system to expand easily with time. The system is based off of a MS Access template "Inventory Management". (HERE)



With a bit of modification, this currently works for creating product invoices and showing what has been used a certain amount of times, but I need to be able to enter a check-out date for when the product is rented, and a check-in date, and have a calendar track the inventory for those dates in-between. This way I can have the system alert me if I am low on the inventory of a certain item, or just report the lowest inventory available for a date range.

The problem I've run into is having the program create a calendar system (or a query) that reports when equipment is available and unavailable based on our stock, and being able to look-up a certain date to see what the equipment status is.

To me this seems like a big request, but I would greatly appreciate a little help or point in the right direction. Thanks everyone!

[PianoMan64]

tomasro:

Since you've asked such a general question, there isn't much that we can do with it. If you can break it down in to small questions that you want to ask as we step through this, I would be willing to assist you with this problem.

Thanks,

Joe P.

[tomasro]

so here's the situation:
I have items with check-out and check-in dates. (2 different entries on a list based on a ProductOrderID)

I need to know how to make a list of dates with those items showing when they check-out and check-in, and that they are unavailable on the days in-between. (I feel like this could be a Query, but I'm new to queries, and I don't know how to make it interpolate the dates not explicitly mentioned in the check-in and check-out dates)

I can then use those results to inform the rest of my database about the status of those items if I can attach a value for those items based on the dates. (I imagine this is the least of my worries)

An appointment calendar that shows multiple people and their overlapping times sounds like a solution (just change people to the rental products). But instead of a visual representation, I need a numerical report of the available items based on the date.

The link above to the template did a great job of helping with current day inventory, but now I need to project future inventory based on these orders. I feel like I'm back in Business Operations 201, in college...odd...

Does this make sense to anyone other than me?

[tomasro]

anyone have an idea on how to help here? Thanks!!!

[PianoMan64]

so here's the situation:
I have items with check-out and check-in dates. (2 different entries on a list based on a ProductOrderID)

I need to know how to make a list of dates with those items showing when they check-out and check-in, and that they are unavailable on the days in-between. (I feel like this could be a Query, but I'm new to queries, and I don't know how to make it interpolate the dates not explicitly mentioned in the check-in and check-out dates)

I can then use those results to inform the rest of my database about the status of those items if I can attach a value for those items based on the dates. (I imagine this is the least of my worries)

An appointment calendar that shows multiple people and their overlapping times sounds like a solution (just change people to the rental products). But instead of a visual representation, I need a numerical report of the available items based on the date.

The link above to the template did a great job of helping with current day inventory, but now I need to project future inventory based on these orders. I feel like I'm back in Business Operations 201, in college...odd...

Does this make sense to anyone other than me?

Hey tomasro,

This functionality that you speak of, isn't something that will be available Out-of-the-box. What you're talking about is a series of Subroutines and procedures that have to be written in VBA in order to support what you're talking about.

First thing we're going to have to deal with would be to get a system of how to count how many vehicals are in or out at any given date, so when you enter a date, the system can tell you how many are either available, or not available depending on what you want to return as a value.

Since this in itself is a very large project, we're going to have to break it down into it's smaller peaces. In order to deal with creating what you want the application to do, I need the following information:

1. I would need to get a detail description of all tables and queries that you've already written so I can get an idea of what you're trying to do.

Example:

TableName
==========================================
FieldName|FieldType |Primary Key|
==========================================
ID Long Integer Yes
First Text(50)
Last Text(50)
Address Text(30)
City Text(15)
State Text(2)
Zipcode Text(10)
etc.

IndexName
=======================================
FieldName| Field Type
etc as Previous example...

Once you completed that and got that back to me, then I can at least start putting this together. Keep in mind that you're going to have to start learning how to program in VBA which is the language that MS Access uses in order to manipulate screen output and features that you would like the application to do, that doesn't come Out-Of-The-Box.

Look forward to your response.

Joe P.

[tomasro]

I am well versed in VBA, so there's no problems there.

Also, as I mentioned before, this is based directly from the Inventory Management template (link above), so the tables and their associations are exactly the same.

I've added a few fields, but the ones that you would be really concerned with are on the Purchase Orders form:
DeliveryDate.....Date/Time (format ie 12/31/09)
DeliveryTime.....Date/Time (format ie 10:00 pm)
PickupDate.......Date/Time (same format)
PickupTime.......Date/Time (same format)

...and I've changed the Products table to include:
Units...............Number (this replaces the auto-generated 'UnitsAvailable' from the orders since that was today based, and not future date based)

I'm not looking for you to do the work for me, but to point me in the right direction since there is nothing available as a sample 'out of the box'. I work best with concepts and some insight on how the program works. I'll figure out the detail stuff and learn in the process.

Thanks for all your help!!!

[PianoMan64]

I am well versed in VBA, so there's no problems there.

Also, as I mentioned before, this is based directly from the Inventory Management template (link above), so the tables and their associations are exactly the same.

I've added a few fields, but the ones that you would be really concerned with are on the Purchase Orders form:
DeliveryDate.....Date/Time (format ie 12/31/09)
DeliveryTime.....Date/Time (format ie 10:00 pm)
PickupDate.......Date/Time (same format)
PickupTime.......Date/Time (same format)

...and I've changed the Products table to include:
Units...............Number (this replaces the auto-generated 'UnitsAvailable' from the orders since that was today based, and not future date based)

I'm not looking for you to do the work for me, but to point me in the right direction since there is nothing available as a sample 'out of the box'. I work best with concepts and some insight on how the program works. I'll figure out the detail stuff and learn in the process.

Thanks for all your help!!!

ok tomasro:

First thing, I took a look at the Inventory system Template that comes with MS Access 2007. It is simply to track Parts, or something along that line.

because of all the modifications to the table structure, and code rewrite that would have to happen, I really would suggest to start from scratch.

Since you are familier with VBA, this will be a great help in getting this project off the ground.

This way we can customize the system exactly the way you want.

So in order to being, we would need to gather some requirements for what you want the system to be able to track, and how detail you need it to be. For example there are many relational tables structures out there on the internet that deal with Hotel reservation systems. and this would be the main template to work from. Since you're have physical items that are rented, they are time based, in your senerio, the hotel structure would be a good place to start.

If you can at least give me an overhead view of what it is that you want to track, this would be a good start to being putting together the table structure.

Thanks,

Joe P.