Query only certain rows?

[10 Gauge]

I'm working on an append query that will take the latest entry minus the previous entry and append it as a single row to an existing table.

What I have worked out so far appends all of the results from from the first table, instead of just one record.



Here is my SQL as of now:

INSERT INTO AssetWKMOUsage ( AssetID, WeeklyUsage )
SELECT AssetUsage.AssetID, DMax("Usage","AssetUsage","AssetID = " & Forms![Asset Details]!AssetID)-DLookUp("Usage","AssetUsage","AssetUsageID = " & [AssetUsageID]-1)
FROM AssetUsage
WHERE (((AssetUsage.AssetID)=Forms![Asset Details]!AssetID));

How can I tell it to ONLY perform the query on the latest entry, and not the entire table?

TIA!

[jzwp11]

If you already have the asset usage why do you need to store it again in another table?

[10 Gauge]

If you already have the asset usage why do you need to store it again in another table?

Hey jzw. I suppose I could just add the needed fields to my AssetUsage table. I don't know why I didn't think of that (idiot *facepalm*).

So I guess the question then becomes how do I have the table autocalculate the weekly and monthly values based off of the input of a new total weekly usage value? (I guess this is where I thought I needed another table so that I could have a query run the calculation and output the results, or something along those lines). I will assume that I will now be using an Update query to do this?

[jzwp11]

how do I have the table autocalculate the weekly and monthly values based off of the input of a new total weekly usage value?

You wouldn't!

You would use a query to do the calculation but you would never store the calculated value. Only under rare circumstances would you store a calculated value. This is not one of those.

[10 Gauge]

You wouldn't!

You would use a query to do the calculation but you would never store the calculated value. Only under rare circumstances would you store a calculated value. This is not one of those.

???

I do need them stored though. I need every weeks mileage amount stored so that we can look back at previous weekly histories for fluctuations in mileage so we can talk to the operator and see why they traveled further/less, etc.

[jzwp11]

You would need an aggregate query that shows the data for each week. I don't remember from our previous discussion, but I assume that you have a date field and a usage value (in a field) corresponding with that date. Can you provide the table/field details and some example data?

[10 Gauge]

You would need an aggregate query that shows the data for each week. I don't remember from our previous discussion, but I assume that you have a date field and a usage value (in a field) corresponding with that date. Can you provide the table/field details and some example data?

That is correct.

Table: AssetUsage
Fields: AssetUsageID, AssetID, Usage, UsageDate, WkUsage, MoUsage, MoIronDays

The Usage field holds the total odometer / hour meter value for the equipment and is updated weekly. The WkUsage field will be used to store the previous weeks usage amount (Usage - Previous Usage), The MoUsage field will be used to store the previous months usage amount (Usage - Previous Usage from 4 weeks prior ([UsageID]-4)), the MoIronDays field will be used to store the monthly iron days amount (Usage - Previous Usage from 4 weeks prior / 8).

[jzwp11]

You do not need these 3 fields in your table: WkUsage, MoUsage, MoIronDays


Additionally, since an asset will have many usage entries you would not update 1 record, but rather add multiple records (one for each asset for each weeking date). Something like this:

AssetUsageID|AssetID|Usage|UsageDate
1|1|50000|3/4/2011
2|2|75000|3/4/2011
3|1|52000|3/12/2011
4|2|77250|3/12/2011

I've attached a sample database. In that database Query1 determines the difference between the weekly usages; this is the most difficult query to construct because it uses 2 subqueries. Query2 uses query1 but just cleaned up to show only records where there is a weekending date. Query 3 shows the monthly values. You can then use a similar scheme as Query1 with Query3 to create a query to get your month-to-month usage differences and calculate your iron days amount.

[10 Gauge]

Wow ok... This is good. I'm going to have to examine this and see if I can figure out what you did to make this happen.

Will I be able to add criteria to only display the query results for the chosen AssetID as per the form control?

[jzwp11]

Will I be able to add criteria to only display the query results for the chosen AssetID as per the form control?

Yes. There are a number of ways to show the data and also to filter it.


You can add criteria to the query itself (WHERE clause).

You can also base a subform on the query and link back to the main form via the assetID.

You can use the DLookup() function in conjuction with the query to pull a specific value for a specific asset and display it on your form.

[10 Gauge]

So I could have the query read the data from an active subform? That seems like the most no-nonsense way to do it.

[jzwp11]

So I could have the query read the data from an active subform?

I'm not sure I understand. Are you saying that you have a subform now and you want to display a value from the query in that subform related to the assetID? I would need a little more detail on the subform (view, recordsource etc.).

[10 Gauge]

Here's the DB so you can take a look, it's on the Asset Details form, very first tab, "AssetUsage Subform". Can I have the query read data straight from there?

After saving my DB it broke somehow, i'll have to see what the problem is....

[jzwp11]

Looking at your asset usage table, the queries I proposed won't work if the usage date is the same (i.e. 3/17/2011) for multiple records with the same assetID unless you store the time component as well as the date. Also, I thought you were going to store the usage once at the end of the week?

A form (or subform) can only be based on 1 table or query, so if you want to see some value or values from the query on the subform you will either have to create a new query that joins the query to the table (that you used for the subform) and make a new subform or use a DLookup() function in a textbox control to display the value you want to see.

As a word of caution, when you use nested or subqueries, you generally can no longer edit the data, so if you base a new query on my original query you will not be able to add any records to the table via subform that would be based on that new query.

[10 Gauge]

Disregard the usage w/ the same date. I was just using the same date (todays date) to make entry faster when I was testing a calculation earlier. When it's in use there will only be one entry per week.