Selecting Data

  • Thread starter Thread starter Geno
  • Start date Start date
G

Geno

Please bear with me. I am 71 and to keep my mind active I
have decided to become familiar with the Access database
and use it for some of my records. I am not new to
computers and am versed in most of the MS applications but
by no means an expert.

My question is in selecting the latest month's entries. I
have a very simple table and a form named Bills. It
contains MonthBillID (key field), Month, Amount and Cost
as fields. Periodicaly data is entered by month date into
the form, such as October 15 2003, 1500, $780.00.

I have a simple query with Month, Amount and Cost. I want
to select the Amount and Cost from items using the latest
month's entry for that item. I can put the name of the
month under the query's criteria and get the data for a
particular month just fine. I want to get the latest data
for each item regardless of when the last entry was made
as some data is recorded every month and some every other
month and some every third month.

If it is easier I can eliminate the day and year and only
use the month's name.

I hope my question is not too stupid and thanks for any
help I can get,
Geno
 
You didn't mention it but if you have a NAME field to your
table and then query on the Name.

If this is your table then:

Name Date Amount Cost

Electric August 1, 2003, 250 $
Mortgage October 15, 2003 1500 $780
Electric November 1, 2003 250 $

The when you query on the "ELECTRIC" you will get all
those items regardless of the date of the entry.
 
Cynthia,

Thanks but that is not the data I am trying to retrieve. I
don't want a report on all of a "Name's" entries
regardless of date. I am looking for the last date any
entry was made. For instance

Date (Month?) (This date being the latest date
Amount
Cost

Geno
 
Could you be a bit more explicit? I am not quite sure what Amount is or what
item is.

If you want the latest Month and Cost for each "Amount" then

SELECT [Month], [Cost], [Amount]
FROM YourTable
WHERE [Month] =
(SELECT Max(T.[Month])
FROM YourTable as T
WHERE T.Amount = YourTable.Amount)

OR you could try two queries. The first query gets the Max(Month) for each
Amount (a totals query) and then you use that query in a join with your table.

QryOne:
SELECT [Amount], Max(T.[Month]) as LatestDate
FROM YourTable

Now use that query and your original table in a joined query where you join The
Amount and the Month fields together

SELECT YourTable.Amount, YourTable.Cost, YourTable.[Month]
FROM YourTable INNER JOIN QryOne
 
John,

Sorry, I am new to this and evidently I am not making
myself clear. I have a table with only MonthBillID,
Month, Amount and Cost fields. I have a form derived from
this table. Enties may be made as follows

July 1432 $106.25
September 1135 $ 96.85
October 1000 $100.00
November 1200 $132.50

Some months may be skipped. I want to generate a report
with the fields Month, Amount and Cost. Whenever the
report is run I want only the Month, Amount and Cost data
from the latest entry. In the above example it would be
November's data. Or maybe the report can ask what month I
want and then give me data only from that month. Either
way will probably work for mw. I hope this is possible and
that I am expresing myself so you understand me.

Thanks,
Geno
-----Original Message-----
Could you be a bit more explicit? I am not quite sure what Amount is or what
item is.

If you want the latest Month and Cost for each "Amount" then

SELECT [Month], [Cost], [Amount]
FROM YourTable
WHERE [Month] =
(SELECT Max(T.[Month])
FROM YourTable as T
WHERE T.Amount = YourTable.Amount)

OR you could try two queries. The first query gets the Max(Month) for each
Amount (a totals query) and then you use that query in a join with your table.

QryOne:
SELECT [Amount], Max(T.[Month]) as LatestDate
FROM YourTable

Now use that query and your original table in a joined query where you join The
Amount and the Month fields together

SELECT YourTable.Amount, YourTable.Cost, YourTable.[Month]
FROM YourTable INNER JOIN QryOne
Please bear with me. I am 71 and to keep my mind active I
have decided to become familiar with the Access database
and use it for some of my records. I am not new to
computers and am versed in most of the MS applications but
by no means an expert.

My question is in selecting the latest month's entries. I
have a very simple table and a form named Bills. It
contains MonthBillID (key field), Month, Amount and Cost
as fields. Periodicaly data is entered by month date into
the form, such as October 15 2003, 1500, $780.00.

I have a simple query with Month, Amount and Cost. I want
to select the Amount and Cost from items using the latest
month's entry for that item. I can put the name of the
month under the query's criteria and get the data for a
particular month just fine. I want to get the latest data
for each item regardless of when the last entry was made
as some data is recorded every month and some every other
month and some every third month.

If it is easier I can eliminate the day and year and only
use the month's name.

I hope my question is not too stupid and thanks for any
help I can get,
Geno
Click to expand...
.
Click to expand...
 
Next questions: Is Month a text field where you type in "November" or is it a
date field where you input a date such as 11/11/2003?
Also, do you have plans to use this in future years?
In the example data, what is the second column - it looks like a number?
Do you only have one entry per month? One entry per month per Amount?

The simplest query I can think of with the grid, would be to put the three
fields from the table in the query. And under the Month field, enter the Month
you want. You can use a parameter query for this. If the Month is a date then
there is an alternative.

Field: Month
Table: YourTable
Criteria: [Which Month:]

If month is a date then you can construct criteria that looks like the data below.

Field: Month
Table: YourTable
Criteria: Between
DateSerial(Year([Enter a Date]),Month([Enter a Date]),1) And
DateSerial(Year([Enter a Date]),Month([Enter a Date])+1,0)

John,

Sorry, I am new to this and evidently I am not making
myself clear. I have a table with only MonthBillID,
Month, Amount and Cost fields. I have a form derived from
this table. Enties may be made as follows

July 1432 $106.25
September 1135 $ 96.85
October 1000 $100.00
November 1200 $132.50

Some months may be skipped. I want to generate a report
with the fields Month, Amount and Cost. Whenever the
report is run I want only the Month, Amount and Cost data
from the latest entry. In the above example it would be
November's data. Or maybe the report can ask what month I
want and then give me data only from that month. Either
way will probably work for mw. I hope this is possible and
that I am expresing myself so you understand me.

Thanks,
Geno
-----Original Message-----
Could you be a bit more explicit? I am not quite sure what Amount is or what
item is.

If you want the latest Month and Cost for each "Amount" then

SELECT [Month], [Cost], [Amount]
FROM YourTable
WHERE [Month] =
(SELECT Max(T.[Month])
FROM YourTable as T
WHERE T.Amount = YourTable.Amount)

OR you could try two queries. The first query gets the Max(Month) for each
Amount (a totals query) and then you use that query in a join with your table.

QryOne:
SELECT [Amount], Max(T.[Month]) as LatestDate
FROM YourTable

Now use that query and your original table in a joined query where you join The
Amount and the Month fields together

SELECT YourTable.Amount, YourTable.Cost, YourTable.[Month]
FROM YourTable INNER JOIN QryOne
Please bear with me. I am 71 and to keep my mind active I
have decided to become familiar with the Access database
and use it for some of my records. I am not new to
computers and am versed in most of the MS applications but
by no means an expert.

My question is in selecting the latest month's entries. I
have a very simple table and a form named Bills. It
contains MonthBillID (key field), Month, Amount and Cost
as fields. Periodicaly data is entered by month date into
the form, such as October 15 2003, 1500, $780.00.

I have a simple query with Month, Amount and Cost. I want
to select the Amount and Cost from items using the latest
month's entry for that item. I can put the name of the
month under the query's criteria and get the data for a
particular month just fine. I want to get the latest data
for each item regardless of when the last entry was made
as some data is recorded every month and some every other
month and some every third month.

If it is easier I can eliminate the day and year and only
use the month's name.

I hope my question is not too stupid and thanks for any
help I can get,
Geno
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top