M$ Access

[Hightower]

Hey all, so it's quarter past 6 on a Saturday night and while everyone is getting ready to go out SAD ACT here is doing college work.

I'm stuck on something in Access which we haven't covered in class at all.

I'm designing queries for a Film CLub (like Blockbuster style shop). I need to get memberships that expire that month but have no idea how?

I also need to get the most rented DVD's for the past three months but I guess if you can help me with the first the second should be straight forward.

I'm guessing something like: "Get users where the month and year of expiry (which is in members table) equals the current month and year"

2nd one: "Sort DVD's in ascending order based on rental record table from current date minus three months upto current date"

I think that's the kind of plain English speak for it but have no idea how to convert this to Access language!

[greatone]

Access does have a query wizard that will let you set a basic query by selecting the table and the fields from it that you want to query.

Once you have the basic query, put it into design view and you can add and remove fields and add criteria to sort of filter fields.

In SQL view you wil have something like:

Select From Members where expirydate = June 08

It is hard to be exact without knowing the structure of the tables and the format of the date.

Hope this helps to at least get you started!

[Hightower]

I know how to build a simple query - it's the hard 'SQL' part that I don't have a clue about.

As I said, I know the plain English - I need to select members who's expiry month and year match the current month and year. I just need help with the syntax.

The table is called tbl_members and the field for the expiry date is called expiry. The format of the date is dd/mm/yyyy

Can anyone help me with the actual syntax of the code?

Thanks,

[Hightower]

Please?

[mac_shinobi]

cant you do a datediff function in access to see the difference between todays date and the date of when they are meant to return it or so many days from the rented out date or something along those lines and then have a warning or do calculations based on how many days it is late by :

Access: DateDiff Function

As for most rented you would prolly require a field that does a running sum total of when it was rented so each time its rented that it adds 1 to the value of the field then its just a case of sorting them by that value ie most rented will have the highest value in this field.

Then if you have a field that has date rented you can just sort that by the last 3 months.

[Hightower]

I managed to get a reply on a specialised Access forum - is anybody awake on a Sunday?! Haha.

He gave me some SQL for the queries along the lines of:

SELECT *
FROM *
WHERE month([Expiry])=month() AND year([EXpiry])=year();

Worked a treat