circular query ... kinda

[Mia]

Hi,

I need to calculate a field INVENTORY in a query based on a couple of
table fields SALES and PURCHASES and previous record's INVENTORY field
that I just calculated. Confusing? Basically,

inventory_calculated : inventory_calculated_previous + purchases -
sales.

I've achieved something similar using DLookup and [Autonumber - 1],
only the DLookup was looking up a table value.

I suspect it might be impossible to do it since it creates a crazy
circular reference. I can't do a subquery either.

A coupla routes I was considering:

1) A field in a table called INVENTORY with a form field bound to it -
form field's default value is all above calculations. Once you hit
enter on the form field, it should punch in the claculated value in the
table field, right?

2) It's QUITE EASILY in Excel Just reference a cel above, and you're
done. Is there a way to integrate Excel? ... Not likely
Got any ideas?

Thank you in advance!

MY

 

[Mia]

Thanks for reply,

All I want to assign a value to a calculated field - that value
includes the previous calculated field value. I've asked many a folks
who just couldn't give me an answer ...

In a nutshell ... You know how sometimes in EXCEL you need to punch in
a formula that contains a reference to one cell above (same formula,
one record earlier)? Emulate that in ACCESS, and you will be deemed a
genious! :0)



Mia

Hi,

I need to calculate a field INVENTORY in a query based on a couple of
table fields SALES and PURCHASES and previous record's INVENTORY field
that I just calculated. Confusing? Basically,

inventory_calculated : inventory_calculated_previous + purchases -
sales.

I've achieved something similar using DLookup and [Autonumber - 1],
only the DLookup was looking up a table value.

I suspect it might be impossible to do it since it creates a crazy
circular reference. I can't do a subquery either.

A coupla routes I was considering:

1) A field in a table called INVENTORY with a form field bound to it -
form field's default value is all above calculations. Once you hit
enter on the form field, it should punch in the claculated value in
the table field, right?

2) It's QUITE EASILY in Excel Just reference a cel above, and
you're done. Is there a way to integrate Excel? ... Not likely
Got any ideas?

Thank you in advance!

MY

Warning about using autonumber in this case:

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Ken Snell [MVP]]

One thought that comes to mind is to add create a query that writes your raw
data into a table (the sales and purchases quantities). This table should
include a field that will allow you to "order" the records -- could be a
date field, or it could be a number field into which your query writes a
sequential number. It also should include a field for your inventory.

Then run a query that updates the table's inventory field for the first
record (using your ordering field to find the "first" record), then that
updates the second record based on the values in the first record, and so
on.

The reason this is "easily done" in EXCEL is because there you have
essentially a table structure, with unique identifiers for each record (a
row number), so the formula knows what the previous record is.

In ACCESS, a query has no such identifier for each record unless you include
a calculated field to give it one -- such as a ranking number that is
calculated by the query. Using such a setup also may be a way to go for what
you want (I have used a similar type of setup for calculating elapsed times
from complicated timeclock data records, and even there I had to use a
temporary table to hold data in order to allow the query to run within a
reasonable amount of time). This is not a simple task in a database (took me
many hours to work out the logic for my project and then to fully debug/test
it under all conditions), and as such goes beyond a "short" answer in a
newsgroup post.
--

Ken Snell
<MS ACCESS MVP>

Thanks for reply,

All I want to assign a value to a calculated field - that value
includes the previous calculated field value. I've asked many a folks
who just couldn't give me an answer ...

In a nutshell ... You know how sometimes in EXCEL you need to punch in
a formula that contains a reference to one cell above (same formula,
one record earlier)? Emulate that in ACCESS, and you will be deemed a
genious! :0)



Mia

Hi,

I need to calculate a field INVENTORY in a query based on a couple of
table fields SALES and PURCHASES and previous record's INVENTORY field
that I just calculated. Confusing? Basically,

inventory_calculated : inventory_calculated_previous + purchases -
sales.

I've achieved something similar using DLookup and [Autonumber - 1],
only the DLookup was looking up a table value.

I suspect it might be impossible to do it since it creates a crazy
circular reference. I can't do a subquery either.

A coupla routes I was considering:

1) A field in a table called INVENTORY with a form field bound to it -
form field's default value is all above calculations. Once you hit
enter on the form field, it should punch in the claculated value in
the table field, right?

2) It's QUITE EASILY in Excel Just reference a cel above, and
you're done. Is there a way to integrate Excel? ... Not likely
Got any ideas?

Thank you in advance!

MY

Warning about using autonumber in this case:

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Mia]

I appreciate all help that I got.

Maybe cranking out basic data in ACCESS and then doing the 'Analyze
with Excel' is the way to go. Is there a way to hit 'Analyze...' and
have ACCESS throw everything NOT in a blank brand new workbook, but
into a preformatted workbook with formulas, hi-lights, etc? Clearly,
ACCESS would generate figures in SAME fields EVERY TIME, so when I
'analyze', I could give it, say, columns 1 thru 20, and columns 21-40
would contain preformatted Excel data, such as formulas, etc tacked on
next to ACCESS data - INCLUDING the formula in question

I would then write everything up in VBA so that everything is seamless,
i.e. punch in a new record in ACCESS, hit a button, view everything in
EXCEL, play with inventory numbers - it does not have to be backwards
compatible with ACCESS thank goodness.

Again, thank you, gentlemen.

Mia Jones

Thanks for reply,

All I want to assign a value to a calculated field - that value
includes the previous calculated field value. I've asked many a folks
who just couldn't give me an answer ...

In a nutshell ... You know how sometimes in EXCEL you need to punch in
a formula that contains a reference to one cell above (same formula,
one record earlier)? Emulate that in ACCESS, and you will be deemed a
genious! :0)

You do know that you can very easily analyze your data with Excel.
Maybe that would be easier.

Mia

Mia wrote:
Hi,

I need to calculate a field INVENTORY in a query based on a couple
of table fields SALES and PURCHASES and previous record's INVENTORY
field that I just calculated. Confusing? Basically,

inventory_calculated : inventory_calculated_previous + purchases -
sales.

I've achieved something similar using DLookup and [Autonumber - 1],
only the DLookup was looking up a table value.

I suspect it might be impossible to do it since it creates a crazy
circular reference. I can't do a subquery either.

A coupla routes I was considering:

1) A field in a table called INVENTORY with a form field bound to
it - form field's default value is all above calculations. Once
you hit enter on the form field, it should punch in the claculated
value in the table field, right?

2) It's QUITE EASILY in Excel Just reference a cel above, and
you're done. Is there a way to integrate Excel? ... Not likely
Got any ideas?

Thank you in advance!

MY

Warning about using autonumber in this case:

I suggest you may not want to use Autonumber for that use.
Autonumbers are designed to provide unique numbers. It in not
designed to provide numbers in order and for a number of reasons may
not do so. As a result using them in any application where the user
sees the numbers is likely to end up with confusion.

There are other ways of providing the numbers you want depending
on the particual application.

 

[Mia]

Joseph,

I've been trying ... I got nothing. My entire project is hanging
because of that one figure ... I suppose the toughest part is to
explain to the superiors how something as simple as copying a formula
in EXCEL could create a problem in ACCESS

Thanks,

Mia

 

[Ken Snell [MVP]]

This can be done via Automation of EXCEL from ACCESS, using VBA.

You'd create a recordset that has your data, initialize EXCEL, open a
workbook (can be one that already exists), write the data into the cells,
and then do whatever else you want to do.


--

Ken Snell
<MS ACCESS MVP>

I appreciate all help that I got.

Maybe cranking out basic data in ACCESS and then doing the 'Analyze
with Excel' is the way to go. Is there a way to hit 'Analyze...' and
have ACCESS throw everything NOT in a blank brand new workbook, but
into a preformatted workbook with formulas, hi-lights, etc? Clearly,
ACCESS would generate figures in SAME fields EVERY TIME, so when I
'analyze', I could give it, say, columns 1 thru 20, and columns 21-40
would contain preformatted Excel data, such as formulas, etc tacked on
next to ACCESS data - INCLUDING the formula in question

I would then write everything up in VBA so that everything is seamless,
i.e. punch in a new record in ACCESS, hit a button, view everything in
EXCEL, play with inventory numbers - it does not have to be backwards
compatible with ACCESS thank goodness.

Again, thank you, gentlemen.

Mia Jones

Thanks for reply,

All I want to assign a value to a calculated field - that value
includes the previous calculated field value. I've asked many a folks
who just couldn't give me an answer ...

In a nutshell ... You know how sometimes in EXCEL you need to punch in
a formula that contains a reference to one cell above (same formula,
one record earlier)? Emulate that in ACCESS, and you will be deemed a
genious! :0)

You do know that you can very easily analyze your data with Excel.
Maybe that would be easier.

Mia


Joseph Meehan wrote:
Mia wrote:
Hi,

I need to calculate a field INVENTORY in a query based on a couple
of table fields SALES and PURCHASES and previous record's INVENTORY
field that I just calculated. Confusing? Basically,

inventory_calculated : inventory_calculated_previous + purchases -
sales.

I've achieved something similar using DLookup and [Autonumber - 1],
only the DLookup was looking up a table value.

I suspect it might be impossible to do it since it creates a crazy
circular reference. I can't do a subquery either.

A coupla routes I was considering:

1) A field in a table called INVENTORY with a form field bound to
it - form field's default value is all above calculations. Once
you hit enter on the form field, it should punch in the claculated
value in the table field, right?

2) It's QUITE EASILY in Excel Just reference a cel above, and
you're done. Is there a way to integrate Excel? ... Not likely
Got any ideas?

Thank you in advance!

MY

Warning about using autonumber in this case:

I suggest you may not want to use Autonumber for that use.
Autonumbers are designed to provide unique numbers. It in not
designed to provide numbers in order and for a number of reasons may
not do so. As a result using them in any application where the user
sees the numbers is likely to end up with confusion.

There are other ways of providing the numbers you want depending
on the particual application.

 

[Mia]

Really good analogy ... I'm in the process of converting everything
I've done in ACCESS to EXCEL. Oh what fun it is to write in VBA today
hahaha

Mia J