subtraction

[Guest]

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

 

[Marshall Barton]

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.

 

[Guest]

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 structure:

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.

 

[Guest]

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.

 

[Guest]

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;
========================

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

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

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;
========================

 

[Guest]

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.

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.

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

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

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

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.

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

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

Hi, Alvin.

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.

I just tested the SQL statement in Access 2000 SP-3 on Windows 2K SP-4 and
I'm not seeing your error message with that version, either. It appears that
Jet/Access on your computer is interfering with the query as soon as you
close it.

Let's compare notes. I've got Jet 4.0 SP-8 and MDAC 2.8 SP-1 on both
computers and the query works on both of them. What service packs does your
computer have for Access, Jet, and MDAC?

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.

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.

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.

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

How and where do I verify that Info?

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

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.

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up menu to
open the Properties dialog window. Select the "Version" tab. Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case anyone else
is wondering, for Windows 2000 msjet40.dll should be version 4.0.9025.0.)

You can also manually check the Microsoft Access executable version number
or use VBA code to find it. The default location for the Microsoft Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.

How and where do I verify that Info?

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

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.


:

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

I Installed the MDAC Component Checker and also checked all and all is the
same as what you listed also my access version is "10.0.6501.0"
This is wierd isn't it. I have no problems with anything else in access. If
you want I can upload it to my website where you can download it if you want
it's up to you.
I really appreciate the help you have given me and only wish I could return
the gratitude.
Alvin Smith

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up menu to
open the Properties dialog window. Select the "Version" tab. Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case anyone else
is wondering, for Windows 2000 msjet40.dll should be version 4.0.9025.0.)

You can also manually check the Microsoft Access executable version number
or use VBA code to find it. The default location for the Microsoft Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.

How and where do I verify that Info?

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

:

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.


:

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

Hi, Alvin.

my access version is "10.0.6501.0"

That isn't Access 2000. You have Access 2002 SP-3! Buggy! Buggy! Buggy!
No wonder. And there's a feature in Access 2002 and 2003 that's not
available in Access 2000 which may be interfering with your query.

Select the Tools -> Options menu to open the Options dialog window. Select
the Tables/Queries tab. Look at the "SQL Server Compatibility Syntax." Is
the check box for "This Database" checked?

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.

I Installed the MDAC Component Checker and also checked all and all is the
same as what you listed also my access version is "10.0.6501.0"
This is wierd isn't it. I have no problems with anything else in access. If
you want I can upload it to my website where you can download it if you want
it's up to you.
I really appreciate the help you have given me and only wish I could return
the gratitude.
Alvin Smith

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up menu to
open the Properties dialog window. Select the "Version" tab. Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case anyone else
is wondering, for Windows 2000 msjet40.dll should be version 4.0.9025.0.)

You can also manually check the Microsoft Access executable version number
or use VBA code to find it. The default location for the Microsoft Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.

How and where do I verify that Info?

:

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

:

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.


:

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

No it isn't. Should it be?

Hi, Alvin.

my access version is "10.0.6501.0"

That isn't Access 2000. You have Access 2002 SP-3! Buggy! Buggy! Buggy!
No wonder. And there's a feature in Access 2002 and 2003 that's not
available in Access 2000 which may be interfering with your query.

Select the Tools -> Options menu to open the Options dialog window. Select
the Tables/Queries tab. Look at the "SQL Server Compatibility Syntax." Is
the check box for "This Database" checked?

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.

I Installed the MDAC Component Checker and also checked all and all is the
same as what you listed also my access version is "10.0.6501.0"
This is wierd isn't it. I have no problems with anything else in access. If
you want I can upload it to my website where you can download it if you want
it's up to you.
I really appreciate the help you have given me and only wish I could return
the gratitude.
Alvin Smith

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up menu to
open the Properties dialog window. Select the "Version" tab. Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case anyone else
is wondering, for Windows 2000 msjet40.dll should be version 4.0.9025.0.)

You can also manually check the Microsoft Access executable version number
or use VBA code to find it. The default location for the Microsoft Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.


:

How and where do I verify that Info?

:

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

:

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.


:

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











:

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;
========================

:

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 structure:

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.


:

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

 

['69 Camaro]

Hi, Alvin.

No it isn't. Should it be?

Nope. If the file isn't too large, please zip it and upload it to your Web
site, and then post the URL (or E-mail the URL to me if this is for my eyes
only -- but it would be better if multiple people can examine it, because
you may get multiple suggestions on how to fix the problem). I'll take a
look at it this afternoon when I can set aside a block of time.

Thanks.

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.)

No it isn't. Should it be?

Hi, Alvin.

my access version is "10.0.6501.0"

That isn't Access 2000. You have Access 2002 SP-3! Buggy! Buggy!
Buggy!
No wonder. And there's a feature in Access 2002 and 2003 that's not
available in Access 2000 which may be interfering with your query.

Select the Tools -> Options menu to open the Options dialog window.
Select
the Tables/Queries tab. Look at the "SQL Server Compatibility Syntax."
Is
the check box for "This Database" checked?

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.

I Installed the MDAC Component Checker and also checked all and all is
the
same as what you listed also my access version is "10.0.6501.0"
This is wierd isn't it. I have no problems with anything else in
access. If
you want I can upload it to my website where you can download it if you
want
it's up to you.
I really appreciate the help you have given me and only wish I could
return
the gratitude.
Alvin Smith

:

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows
explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up
menu to
open the Properties dialog window. Select the "Version" tab.
Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case
anyone else
is wondering, for Windows 2000 msjet40.dll should be version
4.0.9025.0.)

You can also manually check the Microsoft Access executable version
number
or use VBA code to find it. The default location for the Microsoft
Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.


:

How and where do I verify that Info?

:

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

:

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.


:

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











:

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;
========================

:

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 structure:

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.


:

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

 

[Guest]

The address to download it is
" http://www.pittmangamecalls.com/Sales_Breed.zip "
I left the query as is so you can see what it's doing. It is named "
qryWeightGains "
Thanks for the help 69 Camaro. I am doing this for my sister who breeds
Rabbits.
Man those things are going crazy lol
Alvin

Hi, Alvin.

No it isn't. Should it be?

Nope. If the file isn't too large, please zip it and upload it to your Web
site, and then post the URL (or E-mail the URL to me if this is for my eyes
only -- but it would be better if multiple people can examine it, because
you may get multiple suggestions on how to fix the problem). I'll take a
look at it this afternoon when I can set aside a block of time.

Thanks.

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.)

No it isn't. Should it be?

Hi, Alvin.

my access version is "10.0.6501.0"

That isn't Access 2000. You have Access 2002 SP-3! Buggy! Buggy!
Buggy!
No wonder. And there's a feature in Access 2002 and 2003 that's not
available in Access 2000 which may be interfering with your query.

Select the Tools -> Options menu to open the Options dialog window.
Select
the Tables/Queries tab. Look at the "SQL Server Compatibility Syntax."
Is
the check box for "This Database" checked?

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.


:

I Installed the MDAC Component Checker and also checked all and all is
the
same as what you listed also my access version is "10.0.6501.0"
This is wierd isn't it. I have no problems with anything else in
access. If
you want I can upload it to my website where you can download it if you
want
it's up to you.
I really appreciate the help you have given me and only wish I could
return
the gratitude.
Alvin Smith

:

Hi, Alvin.

How and where do I verify that Info?

You'll need to manually verify the Jet service pack. Open Windows
explorer
and find the msjet40.dll file. The default location is:

C:\WINDOWS\system32\msjet40.dll

Right-click on the file name and select Properties from the pop-up
menu to
open the Properties dialog window. Select the "Version" tab.
Msjet40.dll
should be version 4.0.8618.0 for your operating system. (In case
anyone else
is wondering, for Windows 2000 msjet40.dll should be version
4.0.9025.0.)

You can also manually check the Microsoft Access executable version
number
or use VBA code to find it. The default location for the Microsoft
Access
executable is:

C:\Program Files\Microsoft Office\OFFICE<version#>\MSAccess.EXE

Please see the following Web pages:

Retrieve the Microsoft Access executable version number:

http://www.mvps.org/access/api/api0065.htm

Download, installl and run the MDAC Component Checker:

http://www.microsoft.com/downloads/...f6-4a21-4b43-bf53-14332ef092c9&displaylang=en

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.


:

How and where do I verify that Info?

:

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

:

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.


:

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