I am doing an A level project and I need to build a database for a business that sells and orders products.
I have a couple of problems that I hope someone can help me with.
1. Firstly I am trying to make an append query that will move data from my "invoices" table (which contains data on the products sold) to my "archive" table. I would like data from the "invoices" table that is more than three months old to be transferred to the "archive" table. So far I have tried putting different formulas in the "date" field criteria in the query design view. I am not sure what expression to put in and so far everything I have tried has been unsuccessful.
2. I also have a products table. It has the product ID, product name, Minimum quantity, maximum quantity and stock level. I would like to put some kind of validation on the "quantity" field so that when the stock level gets too high or too low, a message appears on the screen to tell the user. I tried typing in an expression in the validation rule box in the table design rule, =between[ProductsMinimumQuantity]And[ProductsMaximumQuantity] But I'm guessing that didn't work because my between values aren't numeric? Is there some kind of query i could perform that I could link to the table or a products form when I open it?
I hope I've made sense and I'm sorry if my problem is too simple or if it seems like im on here to cheat! I'm surrounded by books and tearing my hair out, I don't think my knowledge of databases is enough to do what I want to do!!
<dateadd("d",-90,date()) this will only show data that is Less than 90 days old
could use the <dateadd("m",-3,date()) I like the using the day count
What i have done is written a reorder query that get print each morning @ 6.00am then @ lunch time
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
Thanks that works a treat!! Although now I'm getting an error message saying it didn't add three record due to key violations. It still works I think. this message appeared after I added an extra record to my invoices table.
For the reorder query does it just show products that are not between the maximum and minimum levels, or however you work it out?
So a list is printed of what needs to be reordered?