Access Design Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a design application question. Does anyone have an idea on how to set
up an access application to keep track of gallons going in and out of a large
holding tank? I would like to know the tank level at all times based on
gallons in and gallons out. Also, is it possible to show a graph that
represents the tank level with this data?

Using Access 2000. If it can be done in a later version but not Access 2000
please advise.

Thanks!
 
Table:
tblTankFlow
TankFlowID primary key
Gallons (+ is added to tank, - is removed)
GallonsDate

Query to show current gallons in tank:
SELECT Sum(Gallons) AS GallonsInTank,
Format(Date(),"\A\s\ \o\f\ mmmm\ d\,\ yyyy") AS AsOfDate
FROM tblTankFlow;
 
Hi Ken,

Thanks for the prompt reply. I'll try this and let you know how it works.
I'll post back today.
 
And to get data for a chart that would show the number of net gallons for
each day:

SELECT Sum(Gallons) AS GallonsInTank,
GallonsDate
FROM tblTankFlow
GROUP BY GallonsDate;


To get the cumulative gallons per day (based on adding net gallons for a day
to all previous gallons) is left as an exercise for the reader < g >, or
post back.
 
Hi Ken,

Having a problem with design of the query. Does all this text go in the
criteria section of each of the two fields Gallons and Gallons Date? Please
help explain how I should do this.

Also, In the table you suggest what to you mean in the parenthesis? Does
this mean enter a positive or negative number in the data?
 
Ken,

Sorry, I'm having a problem understanding the Query information you
provided. Please explain. I created the table with no problem.
 
Create a new query. Do not select a table in the initial popup window. Close
that window. On toolbar, click the dropdown arrow on the Query View icon
(usually the first icon on left); select SQL View. Paste the SQL statement
that I posted into the window that you see. Now click the dropdown arrow on
the Query View icon and select Design. You'll now see how the SQL statement
is "created" from the Design view; you'll see the table at the top, field
names at bottom on grid, etc.
 
FL said:
Also, In the table you suggest what to you mean in the parenthesis? Does
this mean enter a positive or negative number in the data?
Click to expand...


The + and - is just to indicate what the meaning of a positive or negative
number is for the data. When you add gallons of liquid to the tank, enter
the gallons as a positive number (with or without the + sign). When you
remove gallons of liquid from the tank, enter the gallons as a negative
number (with preceding - sign).
 
It works! Thank you. I'm going to try the chart now. Sorry, I have no
formal training on writing SQL statements. This must be the Access
Advanced class. My training was years ago I believe in the first versions of
Access that ran with Windows 95 or 98 (I think).
 
It comes from my days in college chemistry classes... often, a more
difficult, challenging question/example was not answered by the textbook --
instead, the author would say "it's an exercise left for the reader". We
often joked that it meant the author didn't know how to solve the problem,
and didn't want to admit it.

But we do know how to "solve" that extra query -- it's just a bit more
difficult than the ones I've posted so far.

< g > is UseNet shorthand for grin. (Just as < s > is smile, etc.)

--

Ken Snell
<MS ACCESS MVP>
 
Table:
tblTankFlow
TankFlowID primary key
Gallons (+ is added to tank, - is removed)
GallonsDate

Query to show current gallons in tank:
SELECT Sum(Gallons) AS GallonsInTank,
Format(Date(),"\A\s\ \o\f\ mmmm\ d\,\ yyyy") AS AsOfDate
FROM tblTankFlow;
Click to expand...

It's a good answer to the question but in practice I wouldn't want to
rely on the SUM of values from day one as a means of avoiding running
empty e.g. rounding errors, evaporation, illicit siphoning, etc. I'd
want regular audits to find the *actual* amount in the tank and for
the query to take account of such stock taking.

Jamie.

--
 
Jamie Collins said:
It's a good answer to the question but in practice I wouldn't want to
rely on the SUM of values from day one as a means of avoiding running
empty e.g. rounding errors, evaporation, illicit siphoning, etc. I'd
want regular audits to find the *actual* amount in the tank and for
the query to take account of such stock taking.
Click to expand...


I agree with you in real practice. An audit could be used to make an
adjustment to the data by adding a correcting record. Of course, if one had
many millions of records for this table, at some point one might want to do
a rollup (likely when an audit is done) sum for faster calculations.
 
Thanks Jamie and Ken - I also agree that we need to validate the accuracy by
audit and make adjustments. This is a good point! Thanks again for the
professional and kind assistance you've provided.
 
In reality, one could have an electronic measuring device connected to a
data acquisition unit, whic would be connected to a PC. The DA unit would
periodically write a file with actual volume at that moment. The PC could
then run a small program to import that data into the database (say every
hour).

I spent many years designing and implementing automated data collection and
control systems, back when America still had a manufacturing base....
 
Back
Top