[EnubreBense]

It's been ages since I've done any of this, need to do a query in MS Access and basicly I want to figure out what I need to put in the criteria box to make it show items of stock which haven't moved for 90 days from todays date.

Any ideas?

[giftbestcom]

We are going to need a little bit more information than that to help you. Like what information are you storing in your database, what are the tables where said data is stored.

But as a stab in the dark, I guess if you had a record of purchases you could do some math between the last purchase of that item and the current date.

[EnubreBense]

We are going to need a little bit more information than that to help you. Like what information are you storing in your database, what are the tables where said data is stored.

But as a stab in the dark, I guess if you had a record of purchases you could do some math between the last purchase of that item and the current date.

Basicly it's a uni project thing, the database is about motor parts.

The tables that have to be used in this query are:
Stock
Products
Suppliers

and the fields to be used are:
Supplier name
Part number
closing stock units
issues this year
date of last movement

I've tried everything, looked across the net etc and things that I think should work just give no results. It's really annoying

[Xfxhbcxp]

Do a Google search for 'SQL datediff' and see if that's helpful. I don't really know anything about Access.

[jokilewqs]

SELECT * FROM STOCK
WHERE DATEDIFF( day, 90, GETDATE() )

Do you have a ProductID field in the Stock table?

[EnubreBense]

SELECT * FROM STOCK
WHERE DATEDIFF( day, 90, GETDATE() )

Do you have a ProductID field in the Stock table?

There's a partnumber field

I tried copying and pasting that criteria but it's saying wrong syntax, I hate Access

[PheliarearY]

i think you would need a union statement as it sounds like you need to pick up data from more than 1 table

actually the tables used sound like a bad way of doing it to me, i deal with sql on a day to day basis for stock items

i would have just the 2 tables of product and supplier, list of suppliers and product table containing stock levels with statement as follows

select * from product where datelastmovement < '90 days ago'

in the correct format

[EnubreBense]

i think you would need a union statement as it sounds like you need to pick up data from more than 1 table

actually the tables used sound like a bad way of doing it to me, i deal with sql on a day to day basis for stock items

i would have just the 2 tables of product and supplier, list of suppliers and product table containing stock levels with statement as follows

select * from product where datelastmovement < '90 days ago'

in the correct format

is that * meant to be there yeah? Ive seen sql ones and Access ones, should that work in access because access keeps bitching at me

[PheliarearY]

is that * meant to be there yeah? Ive seen sql ones and Access ones, should that work in access because access keeps bitching at me

not 100% sure with access tbh, i use sql more than access at work so i cannot remember without looking i am afraid

[jokilewqs]

Do yourself a favour and have a quick read about relational databases. You'll save yourself a lot of pain!

[PheliarearY]

got to work and got chance to check out access stuff

looking at it here you select the tables to include on the query, then each column you specify the table name. then you give a name and select the field you want, then in criteria you specifcy the date range you want

so for example -

table - stock_table
field - Part Numberart_no

and you specify creteri required, i think thats correct anyway

[EnubreBense]

yeah it's the criteria I have trouble with, one of the things it asks you to do for example is show only those items whos movements this year are less than the closing stock figure, that one was easy enough to do. The show items that have movied 90 days from today is impossible, 90 days = 3 months roughly, the data included in the file suggests todays date is 07/99 something round about then, but the solution sheet shows stock items with dateoflastmovement befing in 98?!

7 months = 90 days?! strange

[PheliarearY]

well the only way i can think is you would need to manually enter in a date to give the specification, so you would need to work out products where the last sold date is greater than 90 days ago or whatever

ironically enough i had to so a report for a customer for nearly the exact same query the other day, but was via sql which was easy and i copied the results to excel

[EnubreBense]

Still can't get this atall grrrrrr, anymore ideas, the exam is tommorrow lol

I've tried:

Between Date() And DateAdd("d",-90,Date())

Which really looks like it should work but access gives me a blank screen........

[RLRWai4B]

Try

SELECT field1,field2 FROM table WHERE DateLastMoved < DATEADD('d',-90,DATE())