Hello,

I have a query that searches a running history of parts in my inventory. Because it is a running history, each part has multiple entries. What I would like to do is to have the criteria of a field be set so that it only pulls up the first entry in the history for each part. Anyone know how? Thanks in advance.

Rico

Yes, I know how. If you can provide the name of the table which stores the transactions, and the names of the fields in that table, I can show you how.

Hello,

I have a query that searches a running history of parts in my inventory. Because it is a running history, each part has multiple entries. What I would like to do is to have the criteria of a field be set so that it only pulls up the first entry in the history for each part. Anyone know how? Thanks in advance.

Rico

There is a way of doing this but I am sure other methods will not be as crude as I what I do :D

Firstly, does this table with all the parts entries have a matching table where the part or part number is only one record. If not, you will need to make one for how I do it. If each of your part entries have a common entry such as part number, then make a copy of your table, delete all the recorfds from the copy and then set the parts number field (or whichever appropiate field) for No Duplicates. Then append your original table to the copy.

OK, so assuming we now have a set up where by the table with all the parts entries might have 900 records and the other table has 156 (or whatever) we can proceed :)

If you don't already have it done, make a form for each of these tables.

Now for the next part, I am one of those crude macro users but you convert the following to code.

Lets assume that the common field for both tables/forms is PartsNumber and the form with the 156 records we will call Form1 and the form with all the parts entries we will call Form2.

The first part of the macro is OpenForm and and Form2 will be opened and the Where is [PartsNumber]=[Forms]![Form1]![PartsNumber]

The second action line is SelectObject and that is Form 2. The next action line is GotoControl and that will be the date field in question. The next action is DoMenuItem (I think it is called DoCommand in later versions of Access) then records and the Sort Ascending. Next action is GoToRecord and that is First.

Next action is SetValue. For this you either select a spare field in the table that has the multi parts entries or add a field. Lets call this field Selector.

Item [Forms]![Form2]![Selector]
Expression 1

Next action line is Close and Form is the Object and Form2 is the form.

Next action line is GoToRecord and this is next. This will move things to the next record on Form1.

You now make another macro to run the above macro with the RunMacro action.

The RunMacro action requires a count. You can either put in a number that equals the number of records in Form1 or you can put in on the RepeatExpression

[Forms]![Form1]![PartsNumber] Is Not Null

This stops the macro when the above evaluates to false. Thus it needs to be a field that has an entry on each record. This better than entering the number of records in Count since your number of records will probably change with time.

After the macro has run you will have the number 1 in the earliest date field for each parts entry. So now your query just has 1 as the criteria in the spare field you used or the added field Selector.

Mike

Hi Rico -

This is easily done with a "Select Top 1" totals query.

To illustrate, copy/paste the following into a new Northwind query.

When prompted, enter a product name (e.g. "Geitost", "Pavlova", "Tofu",

"Chang", "Chai")

The result will be the lastest order for the particular product.

Reverse the SortBy to view the earliest order of the product.

Hopefully by studying this you'll be able to adapt it to your needs.

SELECT
TOP 1 Orders.OrderDate
, [Order Details].OrderID
FROM
Products
INNER JOIN
(Orders
INNER JOIN
[Order Details]
ON
Orders.OrderID = [Order Details].OrderID)
ON
Products.ProductID = [Order Details].ProductID
WHERE
(((Products.ProductName)=[enter name]))
GROUP BY
Orders.OrderDate
, [Order Details].OrderID
ORDER BY
Orders.OrderDate DESC;

HTH - Bob