Hello Folks,

I am currently working on a project using Access and ASP, recently I have been tasked to create the ability for users of the system to compare results.

I would try and explain has much as I can, here I go:

The requirment is to create a system enabling users to be able to determine at a click if a user's name exist in the system, 12 months from today comparing the names that exist for this months record, hence I am comparing the names in the system with the names in the system for the past 12 months.

The system does not store monthly record of any sort.

In short, I just wanted to know if you guys can point to the a weblink or books that explains how I can compare results from queries.

A friend told me it was possible in MS SQL, but unfortunately the underlying database is Microsoft Access

Many thanks in advance guys

If i really understand your post, i think the solution is to date stamp every insert query that inserts into the username table. In That case you will be able to fetch results according to dates. By date stamp i mean create a date column and insert the current date with every insert query.

@web master
I actually have a field that does that but I really dont have an hint as to go about it.

Based on another quick on the internet, I realised if I was using MS SQL, I could use the SQL INTERSECT ( http://www.1keydata.com/sql/sql-intersect.html ) statement, but I tested have since found out after testing that Microsoft Access does not support this.

Does anyone know a similar SQL command that could I could use in place of the INTERSECT command

http://www.databasedev.co.uk/ms_access_date_functions.html
Read the link above for more information and experiment. so your answer will now be

*check the formatting of your date or timestamp column to match sure it matches your function formatting. I don't think it will affect it just in case u get problems, think about that

I did not try this yet just writing from what I read and copied lol

DateAdd(period, increment, date)

DateAdd("m", 1, "30-Jan-07" = 28-Feb-07 (adding one month m = month)

DateAdd("yyyy", -1, "30-Jan-07" = 29-Jan-06 (removing one month yyyy = year, my answer may be wrong but you understand the concept. i did not try -1 but it will work)

DateAdd("yyyy", -1, timestamp) = timestamp-1 (that should give you this time last year)

variablenamefortoday = timestamp
variablenameforlastyear = DateAdd("yyyy", -1, timestamp)

So to do your query

you will have

SELECT * FROM tablename WHERE datefield => variablenameforlastyear AND datefield <= variablenamefortoday

loop through your array to search
I hope it works

Why are you using Microsoft Access as your database anyway?. I hope you know the limitations.

web_master:

Why are you using Microsoft Access as your database anyway?. I hope you know the limitations.

Well, I wish someone had told the initial program designer about that. MS SQL would have been my choice but my current task to generate reports based on the records in the database, and I have gone through a lot stress using Access than I would have if I were using MS SQL, but all well and good.

@Cactus

I have got my date selection field orted out already, but many thanks. I have thinking of using the array function, I would give it a short based on what you have explained. Thanks once again

ACCESS sucks man

hey guys,

Not to support access but it does have its place. If you are dealing with, say, a company of 10 people (again, for example), Access is quick and dirty with respect to storing information. For something larger, then you can go for the other DBs.

It all boils down to scalability. Of course, I am not in the shoes of the original developer who chose to use access. For all we know, that is the only thing they knew and/or they decided to hood-wink the client by giving them the bare minimum for the 40K or 50K they probably paid him