Results 1 to 3 of 3
  1. #1
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11

    Question Request to help my build my query

    Hi

    Ten years ago I developed a software in Foxpro. Now, I am converting it to windows based using VB6 as front end and MS ACCESS 2007 as backend.

    I wrote a query in Foxpro as follows:-

    fdint.*, space(30) as membername, 0000000 as fint, sum(int_due) as idue, sum(paidint) as pint, { / / } as ddate, { / / } as rdate from fdint where duedate <= dt1 and type = uchoice and duefalls and member_id = memno group by fd_no order by fd_no into tabl fdidue
    In Access 2007, I tried this query as follows:-

    SELECT fdinterest.*, Space(35) AS membername, 0 AS fint, Sum(fdinterest.int_due) AS idue, Sum(fdinterest.paidint) AS pint, Space(10) AS ddate, Space(10) AS rdate INTO test IN 'D:\Society\Forms\Data\TempData.accdb'
    FROM fdinterest
    WHERE (((fdinterest.[duedate])<=#9/30/2018#) AND ((fdinterest.[membernumber])='01500'))
    GROUP BY fdinterest.fd_no
    ORDER BY fdinterest.fd_no;

    Why I execute this query, I got the following error message.

    CANNOT GROUP ON FIELDS SELECTED WITH '*'(FDINTEREST).

    But, I try the above query with a little change, it gives me the result



    SELECT fdinterest.fd_no, Space(35) AS membername, 0 AS fint, Sum(fdinterest.int_due) AS idue, Sum(fdinterest.paidint) AS pint, Space(10) AS ddate, Space(10) AS rdate INTO test IN 'D:\Society\Forms\Data\TempData.accdb'
    FROM fdinterest
    WHERE (((fdinterest.[duedate])<=#9/30/2018#) AND ((fdinterest.[membernumber])='01500'))
    GROUP BY fdinterest.fd_no
    ORDER BY fdinterest.fd_no;

    (i.e.) I'm taking only one field instead of all fields (*), but I want to all fields in that record.

    Please help to me overcome this problem

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You've already identified the issue.
    You have to list each field separately in both the SELECT and GROUP BY sections of the query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    varadaradj is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2018
    Posts
    11
    Quote Originally Posted by ridders52 View Post
    You've already identified the issue.
    You have to list each field separately in both the SELECT and GROUP BY sections of the query

    Thank you very much. Issue resolved thanks again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-19-2017, 11:08 AM
  2. Replies: 11
    Last Post: 06-15-2016, 07:43 AM
  3. Replies: 1
    Last Post: 10-22-2014, 11:20 AM
  4. Replies: 3
    Last Post: 06-07-2012, 07:06 AM
  5. Replies: 2
    Last Post: 08-29-2010, 12:30 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums