Record selection based on calculation

[WEJ]

Hi All,

Thanks for popping on to have a read, and hopefully provide me with some help. I've been learning as I go with Access and VBA so apologies for the elementary proficiency, but I'm here to learn !

I believe I need to build a query (open to suggestions, hence the believe) that will only return records that fit within the values entered. The complication is that I need the value entered to be used in a calculation to return the the records. To explain further;

I am building a CRM for an advertising company. The company sells advertising space in specific sizes such as A1, A2, A3 etc. Each piece of inventory (piece of advertising space) is allocated a size, A1, A2, A3 etc. When a sales representative makes a sale they can make a sale which is smaller then the size of the Inventory e.g;

Sales Rep B has sold 4 A5 ads to go in Location X in London these are to be split over 4 A3 frames.

As Sales Rep B has sold 4 A5 into 4 A3 there is additional space left over for sale, this additional space needs to be factored in to whether a record is returned or not.

In drawing up an Invoice Sales Rep B is in the process of selecting inventory. The Invoice form has a sub-tab with a sub field in it called Inventory Invoice, this is a sub field as an Invoice can have more than one piece of Inventory.

In the Inventory Invoice sub field there are the following fields;

InvoiceInventoryID - Unique Identifier
IISize - Combo box bound column being size in Inches (this ensures the size is left as a value in the field and not a Key Number)
IIStartDate - The date advertising is to start from
IIEndDate - The date advertising is to end from.
IILocation - The location the Ad is to be placed in.
InventoryID - This should only allow inventory to be selected that is not booked in-between Start and End Dates, and also has enough space left over to fit the ad.
InvoiceID - This is the secondary key for the Invoice table.

To determine whether the Inventory has enough space left the query, or code, will have to perform a calculation. It will have to select the value from from one table and take it away from a value in the inventory table, if the result fits the size entered in the Invoice Inventory table then the user can select the piece of inventory for the booking, however if the ad does not have this available it should display a message box stating this inventory is not available.

It will have to search it's own table for bookings associated to the piece of inventory, pull out the integer and take it away from the IISize (Integer) entered by the user.

To put it into a more logical script this is how I would describe it;

If
IISize = > InventorySize - IISize
And
StartDate To EndDate Not Between StartDate To EndDate
Then Allow Return InventoryID
EndIF
IF
IISize = > InventorySize - IISize
And
StartDate To EndDate Between StartDate To EndDate
Then Do Not Return InventoryID
EndIf
EndSub

You'll have to excuse me with the above, I'm very new to VBA and am not familiar with coding I'm just attempting to write it as logically as possible. Really really need some help so it would be HUGELY APPRECIATED .



Best,

[orange]

We (the readers) do not have any idea of an A5 vs an A3 with respect to space (or any other attribute for that matter). So if we are to help, we have to understand your issue which seems to be clouded with some jargonese. Can you tell us in plain English WHAT you are trying to accomplish -- no code, no Access, less jargon? Tell it as if you were talking to a bunch of 10 yr olds who don't know anything about Access. You have overwhelmed us with concepts, jargon, psuedocode and How you hope to solve this all in one post.

[WEJ]

Hi Orange,

Thanks for the reply.

We sell advertising in A1, A2, A3, A4, A5; this is the size of paper i.e. you write on the an A4 piece of paper. Each location has a variety of size of advertising. A sales person can sell a piece of advertising smaller than the space recorded in the inventory i.e someone sells an A5 piece of advertising into an A3 sheet. This leaves space over. The space left over can fit another 3 A5 adds in it. I want a combo box, query or code that only allows a user to select pieces of inventory that ONLY have the available space and are not booked within the required dates.
I hope that's a bit more straightforward!
Look forward to hearing from you Orange and again, thanks in advanced .
Best,
Will

[orange]

I think I understand the basic concept, however, there must be some algorithm that can divide an A3 sheet into X *A5 etc.

Consider an analogy, you have space for rent (let's say by the day). The space you have can be subdivided "roomed -off" magically by magic doors??
You reserve/record/sell the use of so many sq ft of space to a Customer for a specific day(s). If someone else wants to use your space, you have to determine how many units(sq ft) of space are/will be available on the day that person wants it.
In my analogy I'm using sq ft, but you are using A1, A2, A3, A4, A5 which each represent some number of sq ft.
Let's suppose A1 is your entire space (2000 sq ft in my example say). So if someone wants to reserve space A1 on Jun 1 -all the space is reserved, so new new "occupants " for Jun1. Now if someone wants A2 (1000 sq ft) on Jun 3 you have 1000 sq ft or 1 A2 unit available. Then if someone else wants A3(500 sq ft), and another A4(250 sq ft) and another A5(100 sq ft) all for Jun3, all is okay, you have reserved (1000 +500 +250 + 100 = 1850 sq ft) and have 150 sq ft available.
So if someone needs an A5(100sq ft) you can reserve it. {I know there's still 50 sq ft unreserved, but it doesn't fit out A1-A5 units as I defined them.}

I think what you are saying is that (using my sq ft equivalences) -- someone could reserve an A3(500 sq ft) but only use 3 A5s(300sqft), so you know there is 200 sq ft available. But hasn't this been sold/paid for by the person who reserved the A3?
Are you double-selling the extra 200sq ft?

Hope I'm on the right track.

[WEJ]

Hi Orange,

This is fun , I'm liking the analogies and yes that's bang on.
In regards to your query ... We will rent the space of the location and the amount of space we rent is recorded in our inventory as a specific unit size i.e. A1 etc. We can then rent off the whole space, so all the A1, part of the space, part of the A1, or no space, none of the A1. We do this to spread advertising across the location to ensure fair exposure.
When we record the inventory each size has a corresponding field with floor space in area2 (following you analogy here, it's actually cm and inches in our Db).

...

[orange]

So does the analogy help with setting up the database?

Seems you have Space, and Customers.

A Customer can make 0,1,or more Reservations.
A Reservations is for 1 or more units of space.
A Reservation is made for a certain time period (StartDate, NumberOfDays(contiguous))
(non Contiguous days are handled by separate reservations)
Customers--->Reservation<--SpaceInventory

It would seem that you could query such a database and determine
a)the amount of space available for reservation by day
b)the Customers who have reserved space at any point in time
c)the average/min/max space reservations(by Customer, by Month...)

Good luck.