subtraction

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

Alvin wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22


If you're trying to calculate the difference from one date
to the previous date, then you can not do that in a report.
It needs to be done in the report's record source query:

SELECT animal, weighdate, weight,
(SELECT Max(X.weight)
FROM thetable As X
WHERE X.animal = T.animal
And X.weighdate T.weighdate
) As PrevWeight
FROM thetable As T

With that kind of record source, the weight gain text box
can use the expression:
=weight - PrevWeight

If that's not what you want, please explain and/or provide
an example with 3 or more records.

--
Marsh
MVP [MS Access]

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

.... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

Man O Man this is confusing. I have been studying your example so I can try
and understand. I kinda see but not good enough to build it yet.
I have one table that holds all the info needed.
It is named "Weight" and here are the fields
1. WeightID (AutoNumber)
2. MatingOrderID (LookUp)
3. Date
4. Weight

All those are in the one table named "Weight"
I am getting lost when I see W2.Weight ect Not sure what W2 is for or what
you mean by it?
Thanks for the quick reply.





"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

I'm sorry, I forgot to send my current SQL. Here it is
========================
SELECT Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight
FROM Weight
GROUP BY Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight;
========================

"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

Hi, Alvin.

Man O Man this is confusing.

Don't worry. It's a moderately complex query even for experienced
relational database developers. I'll explain what's going on in a moment,
but first I'd like you to see what the example is trying to show you. You
can create the conditions necessary for this by creating two small tables and
one query (which you can paste in from the SQL I gave you earlier).

(Or, if you are on a tight schedule, you can skip the "demonstration and
explanation" part and jump directly to the part mentioning your table
structure. It starts with the paragraph, "Now back to your table" below.
But come back to the demonstration and explanation part later to find out why
things work the way they do.)

A. Create a new table and give it the following fields:

1. ID, AutoNumber, primary key
2. AnimID, Number (Long)
3. Weight, Number (Single)
4. Unit, Text
5. LogDate, Date/Time

Save the table and name it tblAnimalWeights.

B. Create a new table and give it the following fields:

1. AnimID, AutoNumber, primary key
2. AnimName, Text

Save the table and name it tblAnimals.

C. Open the tblAnimals table and enter two records by typing the name
"Brutus" in the AnimName column for the first record and "Chesty" in the same
column for the second record.

D. Open the tblAnimalWeights table and enter five records for these four
fields:

AnimID, Weight, Unit, LogDate
1. 1, 18, oz., 9/2/2005
2. 1, 40, oz., 9/24/2005
3. 1, 42, oz., 10/5/2005
4. 2, 16, oz., 9/3/2005
5. 2, 31, oz., 9/26/2005

E. Create a new query and paste the following into the SQL View pane:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID)
ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

Save the query as qryWeightGains and open it in Datasheet View. You'll see
the measured weight of each animal on each date and the amount of weight
gained since the measurements began.

F. Create a new report using the Report Wizard:
1. Choose qryWeightGains as the query where the data comes from.
2. Select all fields available in the query to be in the report.
3. Group by AnimID.
4. Don't sort.
5. Use Stepped layout.
6. Use any style you desire.
7. Name the report anything you'd like.

G. Open the report for preview.

Here you see each animal, the dates the animal was weighed, the amount
weighed, and the running total of weight gain.

Now to explain the pieces of the query. When you see W1, W2, or Q, you are
seeing what are called "aliases" for the tblAnimalWeights table. The reason
I'm using aliases is because I want the database engine, Jet, to compare
different subsets (parts of the set) of all the records in one table,
tblAnimalWeights, with different subsets of this same table. So that Jet
doesn't get confused which subset is which, I've identified each subset and
given it a label.

I assigned the label "Q" to this subset of starting dates for each animal
that is weighed:

(SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q

Notice that I've used grouping so that I can get a start date, MIN(LogDate),
for each animal listed in the table. Because I've labeled this subset, I can
later identify any field in this subset with the same named field in another
subset, and Jet will know exactly which fields I want it to use. For
example, Q.AnimID vs. W2.AnimID (the AnimID of another subset):

WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))

Since I want to compare every record with every other record in the
tblAnimalWeights table, I label one set of tblAnimalWeights records as W1 and
the other set as W2, and then I use the operator for the comparison in the
JOIN clause. Notice that I didn't use the =. This is because I don't want
to compare each record with itself.

tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID

I'm using three subsets of the tblAnimalWeights, so I've identified all of
them in the FROM clause of the query, including how I want Jet to consolidate
these subsets into one big set with multiple JOIN clauses:

FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID)
ON Q.AnimID = W1.AnimID

Clear as mud? With more experience, you'll get to the point where all this
stuff is easy. Really. But don't expect it overnight or even in the next
few months. It takes practice.

Now back to your table. First, never use Reserved Words as "identifiers"
(names of tables, queries, fields, procedures, et cetera), because bugs will
infest your program. Some of these bugs will prevent queries, forms, or
reports from opening, while others will give inaccurate data. For example,
using Date (a VBA function) as a field name will, under certain
circumstances, store today's date (i.e., the date the record was created)
where a different date is intended, such as the log date of the weight
measurement. These bugs can be hard to notice and equally hard to track down.

Next, you usually don't want two different items to be named the same
because you don't want Jet or Access to pick the wrong one for you. And you
don't want to get yourself confused about which is which, either.

So, my recommendation is to rename the table to something like tblWeights
and the Date field to something like DateWeighed (or LogDate, which is easier
to type). If you made these recommended changes, then the following SQL
statement would produce the query you want:

SELECT W1.MatingOrderID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID) AS Q
INNER JOIN (tblWeights AS W1
INNER JOIN tblWeights AS W2 ON W1.WeightID W2.WeightID)
ON Q.MatingOrderID = W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

And you can use the Report Wizard and the steps outlined above (section F)
to create the report you need.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

Man O Man this is confusing. I have been studying your example so I can try
and understand. I kinda see but not good enough to build it yet.
I have one table that holds all the info needed.
It is named "Weight" and here are the fields
1. WeightID (AutoNumber)
2. MatingOrderID (LookUp)
3. Date
4. Weight

All those are in the one table named "Weight"
I am getting lost when I see W2.Weight ect Not sure what W2 is for or what
you mean by it?
Thanks for the quick reply.





"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

Your right it is a little complex. Just a little note, I am completetly self
taught with no schooling on vb code or anything of the like. But I have
completed all changes you suggested and the Query works until I try nto make
a report with it or a form in datasheet view. When I attempt this I can go
back to the query and it doesn't work after I try to make a report or form
with it. Here is the SQL.
=====================
SELECT W1.MatingOrderID, W2.LogDate, W2.Weight, (W2.Weight - W1.Weight) AS
WeightGain, W2.Unit
FROM [SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID]. AS Q INNER JOIN (tblWeights AS W1 INNER JOIN
tblWeights AS W2 ON W1.WeightID W2.WeightID) ON Q.MatingOrderID =
W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight, (W2.Weight -
W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;
========================
It gives me the following error when I try to view the query after and only
after I try to generate a report with it.
=====Error message============
The Microsoft Jet database engine cannot find the input table or query
'Select MatingOrderID, Min(LogDate) AS
StartDate FROM tblWeights GROUP BY MatingOrderID'. Make Sure it
exists and that its name is spelled
correctly.
=====End Error Massage=====
Is there anyway to do this on the actual form where I enter the weight info?
Like I said the Query works fine until I attempt to make a Report with it or
a Form.
Thank you for your help and patience











"Alvin" wrote:

I'm sorry, I forgot to send my current SQL. Here it is
========================
SELECT Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight
FROM Weight
GROUP BY Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight;
========================

"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

Hi, Alvin.

It gives me the following error when I try to view the query after and only
after I try to generate a report with it.

The query is too complex to be opened in the QBE grid. It should be
viewable in the SQL View pane, though. However, there should be no
alterations to the query's SQL statement due to merely using the query itself
as a Record Source for either a report or a form, so I'm not sure why you are
being informed that the inline view can't be found. Are you copying this SQL
elsewhere, such as the Record Source Property for either the report or form
and, after attempting to run this report or form, get this error message? Or
perhaps you are using an older version of Access that wasn't capable of
inline views, and I've forgotten about this since I haven't tried to use an
inline view in your version in quite some time? (I'm using Access 2003.
Which version are you using?) As I recall, Jet 3.5 did some screwy things
with SQL when the query was saved. So much so that a copy/paste of the exact
same SQL into a new query would prevent the new query from running without
errors, even when the original ran just fine.

That gives me an idea. Try this: copy and paste the following into a new
query's SQL View pane:

SELECT W1.MatingOrderID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID) AS Q
INNER JOIN (tblWeights AS W1
INNER JOIN tblWeights AS W2 ON W1.WeightID W2.WeightID)
ON Q.MatingOrderID = W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

Save the query, but don't close it. Now create your report or form using
the Wizard and select the name of this new query as the data source. When
finished creating and viewing the new report, open this query in Datasheet
View again. Does it open without error? If so, close it, then open it again
and see whether you get the error.

You may be having trouble with Jet's manipulation of the inline view (those
brackets and the period that Jet replace the opening and closing parentheses
with for the inline view -- that "Q" subquery).

Is there anyway to do this on the actual form where I enter the weight info?

No. This query is a nonupdateable query because of the aggregate function
and the grouping involved. You'll need a simpler data entry form for
entering these records: just the MatingOrderID, LogDate, Weight, and Unit.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

Your right it is a little complex. Just a little note, I am completetly self
taught with no schooling on vb code or anything of the like. But I have
completed all changes you suggested and the Query works until I try nto make
a report with it or a form in datasheet view. When I attempt this I can go
back to the query and it doesn't work after I try to make a report or form
with it. Here is the SQL.
=====================
SELECT W1.MatingOrderID, W2.LogDate, W2.Weight, (W2.Weight - W1.Weight) AS
WeightGain, W2.Unit
FROM [SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID]. AS Q INNER JOIN (tblWeights AS W1 INNER JOIN
tblWeights AS W2 ON W1.WeightID W2.WeightID) ON Q.MatingOrderID =
W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight, (W2.Weight -
W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;
========================
It gives me the following error when I try to view the query after and only
after I try to generate a report with it.
=====Error message============
The Microsoft Jet database engine cannot find the input table or query
'Select MatingOrderID, Min(LogDate) AS
StartDate FROM tblWeights GROUP BY MatingOrderID'. Make Sure it
exists and that its name is spelled
correctly.
=====End Error Massage=====
Is there anyway to do this on the actual form where I enter the weight info?
Like I said the Query works fine until I attempt to make a Report with it or
a Form.
Thank you for your help and patience











"Alvin" wrote:

I'm sorry, I forgot to send my current SQL. Here it is
========================
SELECT Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight
FROM Weight
GROUP BY Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight;
========================

"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

I have a simple way to enter data this is all I have in the form
MatingOrderID, LogDate, Weight
and yes I tried what you suggested and I get the error as soon as I try to
reopen it and also when I created the report it wouldn't open it just
minimized itself.

"'69 Camaro" wrote:

Hi, Alvin.

It gives me the following error when I try to view the query after and only
after I try to generate a report with it.

The query is too complex to be opened in the QBE grid. It should be
viewable in the SQL View pane, though. However, there should be no
alterations to the query's SQL statement due to merely using the query itself
as a Record Source for either a report or a form, so I'm not sure why you are
being informed that the inline view can't be found. Are you copying this SQL
elsewhere, such as the Record Source Property for either the report or form
and, after attempting to run this report or form, get this error message? Or
perhaps you are using an older version of Access that wasn't capable of
inline views, and I've forgotten about this since I haven't tried to use an
inline view in your version in quite some time? (I'm using Access 2003.
Which version are you using?) As I recall, Jet 3.5 did some screwy things
with SQL when the query was saved. So much so that a copy/paste of the exact
same SQL into a new query would prevent the new query from running without
errors, even when the original ran just fine.

That gives me an idea. Try this: copy and paste the following into a new
query's SQL View pane:

SELECT W1.MatingOrderID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID) AS Q
INNER JOIN (tblWeights AS W1
INNER JOIN tblWeights AS W2 ON W1.WeightID W2.WeightID)
ON Q.MatingOrderID = W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

Save the query, but don't close it. Now create your report or form using
the Wizard and select the name of this new query as the data source. When
finished creating and viewing the new report, open this query in Datasheet
View again. Does it open without error? If so, close it, then open it again
and see whether you get the error.

You may be having trouble with Jet's manipulation of the inline view (those
brackets and the period that Jet replace the opening and closing parentheses
with for the inline view -- that "Q" subquery).

Is there anyway to do this on the actual form where I enter the weight info?

No. This query is a nonupdateable query because of the aggregate function
and the grouping involved. You'll need a simpler data entry form for
entering these records: just the MatingOrderID, LogDate, Weight, and Unit.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

Your right it is a little complex. Just a little note, I am completetly self
taught with no schooling on vb code or anything of the like. But I have
completed all changes you suggested and the Query works until I try nto make
a report with it or a form in datasheet view. When I attempt this I can go
back to the query and it doesn't work after I try to make a report or form
with it. Here is the SQL.
=====================
SELECT W1.MatingOrderID, W2.LogDate, W2.Weight, (W2.Weight - W1.Weight) AS
WeightGain, W2.Unit
FROM [SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID]. AS Q INNER JOIN (tblWeights AS W1 INNER JOIN
tblWeights AS W2 ON W1.WeightID W2.WeightID) ON Q.MatingOrderID =
W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight, (W2.Weight -
W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;
========================
It gives me the following error when I try to view the query after and only
after I try to generate a report with it.
=====Error message============
The Microsoft Jet database engine cannot find the input table or query
'Select MatingOrderID, Min(LogDate) AS
StartDate FROM tblWeights GROUP BY MatingOrderID'. Make Sure it
exists and that its name is spelled
correctly.
=====End Error Massage=====
Is there anyway to do this on the actual form where I enter the weight info?
Like I said the Query works fine until I attempt to make a Report with it or
a Form.
Thank you for your help and patience











"Alvin" wrote:

I'm sorry, I forgot to send my current SQL. Here it is
========================
SELECT Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight
FROM Weight
GROUP BY Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight;
========================

"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin

I'm sorry, I'm using access 2000 and windows XP

"'69 Camaro" wrote:

Hi, Alvin.

It gives me the following error when I try to view the query after and only
after I try to generate a report with it.

The query is too complex to be opened in the QBE grid. It should be
viewable in the SQL View pane, though. However, there should be no
alterations to the query's SQL statement due to merely using the query itself
as a Record Source for either a report or a form, so I'm not sure why you are
being informed that the inline view can't be found. Are you copying this SQL
elsewhere, such as the Record Source Property for either the report or form
and, after attempting to run this report or form, get this error message? Or
perhaps you are using an older version of Access that wasn't capable of
inline views, and I've forgotten about this since I haven't tried to use an
inline view in your version in quite some time? (I'm using Access 2003.
Which version are you using?) As I recall, Jet 3.5 did some screwy things
with SQL when the query was saved. So much so that a copy/paste of the exact
same SQL into a new query would prevent the new query from running without
errors, even when the original ran just fine.

That gives me an idea. Try this: copy and paste the following into a new
query's SQL View pane:

SELECT W1.MatingOrderID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID) AS Q
INNER JOIN (tblWeights AS W1
INNER JOIN tblWeights AS W2 ON W1.WeightID W2.WeightID)
ON Q.MatingOrderID = W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

Save the query, but don't close it. Now create your report or form using
the Wizard and select the name of this new query as the data source. When
finished creating and viewing the new report, open this query in Datasheet
View again. Does it open without error? If so, close it, then open it again
and see whether you get the error.

You may be having trouble with Jet's manipulation of the inline view (those
brackets and the period that Jet replace the opening and closing parentheses
with for the inline view -- that "Q" subquery).

Is there anyway to do this on the actual form where I enter the weight info?

No. This query is a nonupdateable query because of the aggregate function
and the grouping involved. You'll need a simpler data entry form for
entering these records: just the MatingOrderID, LogDate, Weight, and Unit.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

Your right it is a little complex. Just a little note, I am completetly self
taught with no schooling on vb code or anything of the like. But I have
completed all changes you suggested and the Query works until I try nto make
a report with it or a form in datasheet view. When I attempt this I can go
back to the query and it doesn't work after I try to make a report or form
with it. Here is the SQL.
=====================
SELECT W1.MatingOrderID, W2.LogDate, W2.Weight, (W2.Weight - W1.Weight) AS
WeightGain, W2.Unit
FROM [SELECT MatingOrderID, MIN(LogDate) AS StartDate
FROM tblWeights
GROUP BY MatingOrderID]. AS Q INNER JOIN (tblWeights AS W1 INNER JOIN
tblWeights AS W2 ON W1.WeightID W2.WeightID) ON Q.MatingOrderID =
W1.MatingOrderID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.MatingOrderID = W2.MatingOrderID))
GROUP BY W1.MatingOrderID, W1.LogDate, W2.LogDate, W2.Weight, (W2.Weight -
W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;
========================
It gives me the following error when I try to view the query after and only
after I try to generate a report with it.
=====Error message============
The Microsoft Jet database engine cannot find the input table or query
'Select MatingOrderID, Min(LogDate) AS
StartDate FROM tblWeights GROUP BY MatingOrderID'. Make Sure it
exists and that its name is spelled
correctly.
=====End Error Massage=====
Is there anyway to do this on the actual form where I enter the weight info?
Like I said the Query works fine until I attempt to make a Report with it or
a Form.
Thank you for your help and patience











"Alvin" wrote:

I'm sorry, I forgot to send my current SQL. Here it is
========================
SELECT Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight
FROM Weight
GROUP BY Weight.WeightID, Weight.MatingOrderID, Weight.Date, Weight.Weight;
========================

"'69 Camaro" wrote:

Hi, Alvin.

In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain

One needs to do this calculation in the query that will be used as the
Record Source for the report. In the following example of calculating the
accummulative weight gain (or loss) for each animal, the table uses the
following structu

Table Name: tblAnimalWeights
1.) ID, AutoNumber, primary key
2.) AnimID, Number, foreign key to tblAnimals (to identify individual
animal records)
3.) Weight, Number
4.) Unit, Text (measurement: lbs., oz., et cetera)
5.) LogDate, Date/Time (date weight recorded)

Example query:

SELECT W1.AnimID, W2.LogDate, W2.Weight,
(W2.Weight - W1.Weight) AS WeightGain, W2.Unit
FROM (SELECT AnimID, MIN(LogDate) AS StartDate
FROM tblAnimalWeights
GROUP BY AnimID) AS Q
INNER JOIN (tblAnimalWeights AS W1
INNER JOIN tblAnimalWeights AS W2 ON W1.ID W2.ID) ON Q.AnimID = W1.AnimID
WHERE ((Q.StartDate = W1.LogDate) AND (Q.AnimID = W2.AnimID))
GROUP BY W1.AnimID, W1.LogDate, W2.LogDate,
W2.Weight, (W2.Weight - W1.Weight), Q.StartDate, W2.Unit
ORDER BY W2.LogDate;

... where WeightGain is the accumulated weight gain (or loss).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"Alvin" wrote:

I have a weight Report that shows specific Dates and weights of animals.
In the report instead of having a running sum is there a way to have a
running subtraction so I can show the amount of weight gain, In Other words
say I have the following

Date Weight
9/3/05 18 oz
9/23/05 40 oz

A running sum shows the following

Date Weight Sum
9/3/05 18 oz 0
9/23/05 40 oz 58

I want it to show like the following instead of the way it is above.

Date Weight Weight Gain
9/3/05 18 oz 0
9/23/05 40 oz 22

Thanks for any Ideas
Alvin