On previous query

C

cager

Hi there,

I'm fairly new to Access.

I have a query that I run every day that that produces a list of part #,
whse #, part description (if the part has 0 onhands).

The next day I will run the same query and some records will fall off the
query (if the part came back into stock i.e. Onhands >0).

What I want to do is add a field that shows if the part/loc that's on my
list today was also on the list yesterday.
 
W

Wayne-I-M

Hi

Not really sure how you are filtering your query. I assume you have some
form of criteria that has a Onhands count of more than 0.

The problem with this - as you point out
The next day I will run the same query and some records will fall off the
query (if the part came back into stock i.e. Onhands >0).
Click to expand...
The record with a OnHands on more than 0 today may not have had them
yesterday and so will not show in the query.

I think it may be an idea to have 2 queries. One with a filter on Date()
and another with the filter Date()-1.

Combine these queries and total on PartID (with a criteria of >1) – this
will list the parts in both queries. - Unless the same part has a OnHands of
more than 1 on the same date.

Not really sure how your DB or queries work so can’t be but more help
without more info.
 
W

Wayne-I-M

ooops - sorry just re-read your post,

You only need 1 query - ignor my last.

Set your query criteria to
Date() Or Date()-1
Make it a totals query and total on the OnHands Date field

The simple answer are always the best. LoL
 
T

Tom Wickerath

Hi Wayne,

I'm not quite sure that using Date() Or Date()-1 is the correct criteria....

If I understand the situation correctly, in order for a record to be
included on any particular day, the Onhands field must equal zero. I think
the only way to achieve this functionality will be to store the Onhands
value, with a last updated date stamp, along with the previous Onhands value
just prior to the last update.

Cager should use a form for all data changes (ie. not making data changes
directly within a table or query), with some simple VBA code to record when
the Onhands value was last updated. This code would also need to move the
current Onhands value to another field, perhaps named PreviousOnhands.

I'm thinking that one would then run the first query as is, and UNION the
results with another query that uses PreviousOnhands=0 with OnhandsUpdated =
Date(). Something like that...


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
C

cager

I think I may need to clarify Tom. My criteria for when I run this query is
Onhands >0 but if a record falls off the report today that had onhands <0
yesterday that's fine. That doesn't really concern me.

The main reason for this query is that it produces a list for rebuyers to
use each day to rebuy product that have 0 onhands. The rebuyers have asked
that I include a "on pervious list" field with values of either yes or null.
After the query is done running each day I copy and paste it into excel where
the rebuyers filter on this "on previous date" field and filter out the
"yes's" so that they are not looking at and rebuying the same parts each day.

Does that help a little? So what I really need is a way to say if record 1
was on yesterday's query and is on today's query then say "yes on previous
list", else null.
 
C

cager

sorry just re-read my post and noticed a type. My criteria for this query
is onhands < 0.
 
T

Tom Wickerath

Can you show the following?

1.) The structure for the table(s) involved
-->table and field names, data types, indexed fields--specifically, the
primary key(s)

2.) Some sample data

3.) The SQL (Structured Query Language) statement for your query. This is
available by opening your query in design view, and then clicking on View >
SQL View. Please copy the SQL statement, and paste into a reply.

4.) The results this query is returning for the sample data on hand.

5.) The desired results of this query.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
C

cager

Tom,

Hopefully this will give you a better idea to what I need help with.

1.) The table structrue is the following

TableName: tbl_ABC Out of Stocks

Field Names: Rebuyer(tesxt), Venid(number), vendor(texxt), part(number),
partdsc(text), Loc(number), OHQTY(number), OOQTY(Number), On Prev List (text)

The primary keys are loc and part. There are some other fields in this
query, but these are the most important for the purposes of the rebuyers
using this report on a daily basis to buy products with Onhands<=0 (in our
system we can have onhands less than zero if there is a customer backorder
present)

2.) Some sample data:

TEAM REBUYER VENDID VENDOR PART DSC LOC OHQTY OOQTY ON PREV. LIST
Team VAPLON BWILLIAM 11261 AIR COOL INDUSTRIAL 269102 52" 5-BLADE DUAL-MOUNT
FAN W/ LIGHT KIT 27 0 30 YES
Team VAPLON BWILLIAM 11261 AIR COOL INDUSTRIAL 261321 52" 5 BLADE DUAL MOUNT
PADDLE FAN 17 0 125 YES

3.) The SQL code:

SELECT Buyers.Team AS TEAM, [tbl_ABC Out of Stocks].REBUYER, [tbl_ABC Out of
Stocks].VENDID, [tbl_ABC Out of Stocks].VENDOR, [tbl_ABC Out of Stocks].PART,
[tbl_ABC Out of Stocks].DSC, [tbl_ABC Out of Stocks].LOC, [tbl_ABC Out of
Stocks].SHPLOC, [tbl_ABC Out of Stocks].POLOC, [tbl_ABC Out of
Stocks].STOCKED, [tbl_ABC Out of Stocks].ITMDSG, [tbl_ABC Out of
Stocks].PARTTYP, [tbl_ABC Out of Stocks].STKMETH, [tbl_ABC Out of
Stocks].EFFDT, [tbl_ABC Out of Stocks].ORDBEGDT, [tbl_ABC Out of
Stocks].DISCDT, [tbl_ABC Out of Stocks].OHQTY, [tbl_ABC Out of Stocks].OOQTY,
[tbl_ABC Out of Stocks].[QTY IN PC]
FROM [tbl_ABC Out of Stocks] INNER JOIN Buyers ON [tbl_ABC Out of
Stocks].REBUYER = Buyers.Lid
WHERE ((([tbl_ABC Out of Stocks].ITMDSG) In ("A","B","C")))
ORDER BY [tbl_ABC Out of Stocks].ITMDSG, [tbl_ABC Out of Stocks].REBUYER,
[tbl_ABC Out of Stocks].VENDOR;

4.) I believe item #2 is the results that the query products.

5.) As you can see there is a column header named "on previous list".
Right now this value is being populated on a manual basis. I get the output
from the query and paste it into Excel. I then open up yesterday's list and
do a vlookup onto today's list to see if a part was on yesterday's list and
is still present today.

what I'd like is for Access to do this for me. Somehow take a look at
yesterday's data and tell me if the same part/loc combination is on the list
today.

Hope that helps Tom. I know this explination is long winded but any help
you can provide would be greatly appreciated.

Thanks!
 
Top