How to Turn a Record Of Data Into Something Else

[doyle60]

I completed a query that seemed to work except for the fact that it
became too complex and Access simply refused to run it. I tried
splitting it into parts but it was still too complex.

Here is the problem. I have a record of data for On Hand (goods in a
warehouse) and Projections (predicted needs) per month. So for one
record (an SKU, five or six keys) I may have these for units (OH = On
Hand):

OH M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
1000 200 100 500 300 100 150 150 200 600 100
100 120

The M01, M02, etc. are fixed fields I created with M01 always the
current month.

What I want to do is to make a query that turns this into what I need
to order for future production. In the above that 1000 On Hand will
last until M04 with 100 short. So I need 100 for M04. But I also
have to order three months of inventory at a time so I want that 100
balance to be added to the next two months needs (100 and 150) to get
350. Than I want to put in what the next production orders should
be. So the above line should turn into this:

OH M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12
1000 000 000 000 350 000 000 950 000 000 320
000 000

Since shipping generally takes a month and orders should be placed
three or four months in advance, I was simply going to change the
month names on reports and forms with code. There is no need to go
into it if it is a simple shift of data.

Anyway, how do I get the database to return the above?

Thanks,

Matt

 

[dch3]

You're not so much as looking for a query as you are looking for record by
record processing as in DAO. DAO allows you to programically loop through
records and work with them. For some situations it can be much easier than
running a SQL Statement. Here are some references to look into as well as the
basic code that'll get things started.


The example below cycles through the controls on a form and then
programically inserts values into the table "_controlSnooper". The rst Object
does all of the work with the key methods being .AddNew and .Update. To edit
an existing record, you'd use a SELECT query in place of the table name as in
Set rst = db.OpenRecordSet("SELECT * FROM tblParts WHERE lngPartId=54") and
then use the .EDIT method to update the values.

Now in your particular situation, you're looking at two steps.
First, you'll need to adjust the values to account for what you have on
hand. Second, you'll need to adjust the values to add in the month order. The
logic would be something like this...

..Edit
[Place the On Hand value into a variable]
[Check if you have enough on hand to cover M1]
[YES - subtract M1 from On Hand variable and set M1 to 0]
[NO - subtract On Hand variable from M1 value and set M1 to the difference]
[Add monthly order to the new value of M1]
[Move on to the next Month]
..Update

I don't recommend a query per se as it you'll have to have logic in place to
account for the values that you have on hand and the fact that you won't have
enough on hand to cover the next 12 months.

Personally, in whatever SUB you place the code in, I'd also have code that'd
sum up the values based on whatever order information you have in the
database so that the starting values are always up to date.

EXAMPLE CODE:
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("_controlSnooper")

For i = 0 To Forms(strForm).Controls.Count - 1
rst.AddNew
rst("txtForm") = strForm
rst("txtName") = Forms(strForm).Controls(i).Name
rst("txtType") = Forms(strForm).Controls(i).ControlType
rst("dteCaptureDateTime") = Now
rst("txtVisible") = Forms(strForm).Controls(i).Visible
rst("txtDefaultValue") = Forms(strForm).Controls(i).DefaultValue
rst("txtControlSource") = Forms(strForm).Controls(i).ControlSource
rst.Update
Next i

rst.Close
Set rs = Nothing
Set db = Nothing

 

[dch3]

Forgot the documentation:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

(Contains information on ADO and some references to ASP, but the same
principles apply...)
http://www.classanytime.com/mis333k/sjdaoadorecordsets.html

 

[doyle60]

Thanks so much for spending the time to write all that. But I'm
afraid I don't understand it at all. I think it a bit difficult to
learn it in a day, a week, or even a month. I should have learned
such things by now but just haven't had the time to educate myself on
such things.

I'm afraid, unless you are willing to walk me through it, I'd have to
pass. My bosses just wouldn't allow me to spend so much time learning
it anyhow.

Also, if I understand you correctly, this will take updating tables,
and I should give up hope on having the query work live in a form as
one edits data.

I will continuing trying my method but will throw data to tables to
avoid the too complicated issue.

If you pass on walking through it with me step by step, I understand,
believe me.

Thanks again.

Matt

 

[dch3]

Email me a copy of the database to (e-mail address removed)

I should be able to work something up by the end of the week

 

[doyle60]

Thanks so much for that. But my database is huge and is over ten
years old, split and used by 50 or more people. I could make you a
database and send that to you, however. But I am having success with
this by pasting data to tables in intermediate steps.

But I may want to do something more with it and if so, I just may send
something to you. Thanks,

Matt