Select / Dlookup

[Rene Schepers]

Dear Bytes Users

I have created a database for fuel consumption for various vehicles. By entering the date, plantno, mileage_reading and liter. I managed to get the previous mileage_reading via a select statement in query:
- created a query from a table fuelbook and made an alias fuelbook1
- created a new field

Prevmeter: (Select max(meter) from fuelbook Where meter < fuelbook1.[meter] and plantno = fuelbook1.[plantno])

Till there is works perfect and I can retrieve the previous data for the meter reading

Now I am trying to retrieve the previous liter which is depending on previous record, with the same plantno.

I tried it with Dlookup were I can get the previous record but not for the specific plantno, it give only the liter previous ID (as I used ID-1)

I tried also

Prevliter: (Select (liter) from fuelbook Where liter < fuelbook1.[liter] and plantno = fuelbook1.[plantno])

But this gives no result

Anybody who can get me in the right direction

[zmbd]

@Rene

Welcome to the bytes forum!

I think that there may be only a few people that really understand what you're asking... and I'm lost too...
However it's very late/early and I haven't had my nap yet since yesterday so please forgive me if I seem a tad tuttle-and-mule with your question.

Would you please review your question and provide more detail as to what it is you're having issues with?

However, help us to help you - you just might want to review the following threads before re-posting your question:
Posting Guidelines
How to ask good questions
FAQ

Before you get too much farther into your project may I respectfully offer a few links that might help you with your current and future projects:

I’ve found that all too often the textbooks really do a very poor presentation of the actual steps behind setting up a database from scratch. However, I have found a really good step by step tutorial for basic design here (and a good review for those that have done this a few times too IMHO - was for me :-) ): A Tutorial for Access

Although the afore mentioned may sound a tad late for your current project, this tutorial may yet give you some insight to any issues you may run into with your project in the near term and should help you with any future projects.

Of particular note is >> tables <<page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy grid table to do this... just a line by line layout.

One of the hardest concepts for most people to understand is that of "normalization." I found this link to have a really good really good explanation of the concept for those just starting out and I found it to be a good review when I read thru it: Database Normalization and Table Structures.

Looking forward to helping you with your project...

-z

[Rene Schepers]

Many thanks for your quick reply and assistance

OK I have been programming with access for a good while and try to create more to my job specific databases and features.

I just read the links you posted which are clear to me but not any contribution to a solution

Let me show a small part of my table to clarify

Tablename: Fuelbook

ID Date plantno meter Prevmeter mileage liter Prevliter
1 09.05.12 TO 94 91968 64
2 09.05.12 TO 85 97365 52
3 12.05.12 TO 94 92589 91968 621 67 ??64?? (to be extracted by code)
4 12.05.12 TO 85 98200 97365 835 63 ??52?? (to be extracted by code)

For me to compute the fuel consumption for example Plantno TO 94,

I calculate the mileage (meter-prevmeter) and need to divide that figure (621) through the liters previously filled "prevliter" (64) to get ltr/km

Basicly I want to Dlookup or select a previous record (liter) for that specific plantno

DLookup examples are given on the internet but do not relate to filtered records but just a field and an ID
Expr1: DLookUp("[MyField]","MyTable","[ID]=" & [ID]-1)
The above gives me the liter of the previous ID rather than the previous plantno TO 94


Hope things are a bit clearer explained now.

Regards

Rene

[twinnyfo]

Rene,

I hope this will help. I can't write the query for you, but you may have to use two queries to satisfy your question.

First, create a query whose sole purpose is to identify the record ID of the record you want. Use an aggregate query, and in stead of the group by, use the Max of the last fill up meter reading, limited by all those record that met the PlantNo criteria of the latest fillup. You will also want to exclude the current record.

Once you have that Record ID, use that ID to gather whatever data you need from the table from that record. You would join the table to that query in the second query and the data from just that record should be the only data displayed.

Again, I hope this helps, bu tthese are the steps that I would go through. Some others may have some better ideas. Try some things and if you hit any snags, we will be glad to assist!

[zmbd]

Rene
Thank you for reading thru the links I posted.
They were not intended to provide a solution... in as much as the question you asked was not clear.
Instead the links I posted were intended to help you help us by way of providing a guideline for you to post your question.
As you can see, Twinnyfo was able to provide some guidence from your second post.
One thing that would help with how you posted the example data would have to used a comma or semi-colon between the field names and the data.
-z