Windows XP 
Access 2003
Hi. I am new here but have been using Access basics for years. I'm a beginner in terms of Access know-how, but I'm discovering new things daily in my quest to keep track of supplies we distribute to my organization's different offices.
In distributing the supplies, we are methodical in doling out just what is necessary. So, many offices get a mix of full boxes and 'singles'. Smaller offices might just get 'singles'. Currently I have the two number columns in a table: "Boxes" and "Singles". I keep a different table (linked through a relationship) where I keep up with details on how the different products are packaged - some have 30 items per box, some have 12, 20 or 24.
PROBLEM: We needed to know what these offices have on hand and what they’ve used.
This information will help us report what has been depleted and what we have to replenish to bring them up to full inventory. So where at first an office may have received 10 boxes and 14 singles (in boxes packed with 30 items per box), they may have told us that they have 6 boxes and 22 singles left.
In a report, a math equation would tell me that they used up 4 boxes and -8 singles. Clearly, this doesn't work. So, I am doing a workaround fix where I convert my boxes and singles into a single column that multiplies boxes with 'factor' and then adds the single. I will in the future make this data permanent and not a formula. In this way, I will have a column that tells me that the office has 314 widgets ((10*30)+14). When the office reports to us that they have 6 boxes and 22 singles, I will create a new line with a -202 ((6*30)+22); I’ll have to do that math myself and manually (I think?). I could then have a report that tells me that that office needs 112 widgets.
How can I then set up a report where I can have the 112 widgets re-broken down into boxes and singles, according to that item’s respective multiplying factor?
Reply With Quote
