Inventory Database

  • Thread starter Thread starter Connie Wickam via AccessMonster.com
  • Start date Start date
C

Connie Wickam via AccessMonster.com

I am working on creating a timeshare sales and inventory database. I am
having difficulty with the inventory. The problem is that one unit could
be sold either by odd years, even years or consecutive years. If a unit is
sold for consecutive years then I need it to be removed from the available
inventory, if a unit is sold for either odd or even years I need the
opposite year to stay in available inventory and consecutive would not be
an option.

Does anyone have any ideas on how to do this the simpliest way.
 
Hi Connie,

How I would solve the issue:

Create an option group on the Sales form with 4 option buttons or a combobox
with a list of 4 items: Unsold, Consecutive, Even, Odd. You might include
a date field to indicate the day the unit actually sold. What ever the
mechanism, call it SalesStatus.

Thereafter, I would refer to the value of SalesStatus in the main record and
calculate the availability for reports, etc. by using a Select --- Case
statement and put the code into a function procedure in a general module
that you call from your query.

Private Function Availability(SalesStatus as string) as string

Select SalesStatus
Case Unsold
Availability = "Available"

Case Consecutive
Availability = "Unavailable"

Case Even
If format(Date,"YYYY") mod 2 =0 then
Availability = "Unavailable"
Else
Availability = "Available"
End If

Case Odd
If format(Date,"YYYY") mod 2 =0 then
Availability = "Available"
Else
Availability = "Unavailable"
End If

End Select

Change the return value to be words that fit your application. Always
calculate the results when you need them rather than save them in the
database. The above is all "air" code so your mileage may vary. If you
want to show only the available units then, I'd create a new field in my
query: UnitsAvail: =Availability(SalesStatus) Down in the criteria line
put "Available" and you will see only those units that are available this
year.

HTH
 
Ok, I understand the select case, but I don't is when you say the above is
"air" code. I am fairly new at programming in VB code, I have written a
few basic databases to track information, but this one is a little more
difficult. I know the basics, would you be so kind as to elaborate on it.
This is a great solution, thanks so much for your assistance.
 
Hi Connie,

"Air code" means I pulled it out of the air. I've used the solution before
but haven't referred to old code just now. I just wrote whit I believe to
be true: It's a caveat that you can't just cut and paste and be sure it
will work without further reasoning and modification.

HTH
 
Jan 1, 2005 to Dec 31, 2005

Jan 1, 2007 to Dec 31, 2007





As you can see, you get maximum flexibility here. For requesting a
room/resource that is available, it is a simple matter to query the booking
table.



Further, the above approach means no fixed dates, and you can use the
software for properties that might be booked (split) in one year, or even 6
months for one person, and 6 months for another party. And, even more
interesting is that you can overlap years, since perhaps some dates might
always start in February, Or June, and go for one year.



So, building a simple date booking table will give you VERY high
flexibility, and actually reduce the amount of code you need to write also.
 
Back
Top