+ Post New Thread
Results 1 to 6 of 6
How do you do....it? Thread, M$ Access in Technical; Hey all, so it's quarter past 6 on a Saturday night and while everyone is getting ready to go out ...
  1. #1

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241

    M$ Access

    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!

  2. #2

    Join Date
    Jun 2008
    Location
    Essex
    Posts
    47
    Thank Post
    2
    Thanked 17 Times in 15 Posts
    Rep Power
    15

    M$ Access

    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!

  3. #3

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241
    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,

  4. #4

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241
    Please?

  5. #5

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,897
    Thank Post
    3,420
    Thanked 1,081 Times in 997 Posts
    Rep Power
    369

    DateDiff

    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.

  6. #6

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Access LAN but no net??
    By contink in forum Windows
    Replies: 6
    Last Post: 24th June 2007, 11:43 PM
  2. HD detected but cannot access
    By Busybub in forum Hardware
    Replies: 10
    Last Post: 15th March 2007, 04:48 PM
  3. MS Access
    By wesleyw in forum How do you do....it?
    Replies: 1
    Last Post: 9th November 2006, 09:50 AM
  4. FTP Access
    By ben_hampshire in forum Windows
    Replies: 7
    Last Post: 27th April 2006, 11:44 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •