Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Complex Query dealing with data in a range

    The program deals with issues of cheques, to customers, they then redeem products against these cheques.

    I have two tables containing the following fields

    tblChqIssued
    .Project_Num (Holds the project number)
    .Start_Num (Holds the starting cheque number issued)
    .StopNum (Holds the ending cheque number issued)

    tblRedeemed
    .ProjectNum (Holds the project number)
    .ChqNum (Holds the cheque number redeemed)

    Requirements
    1.) To return the total value of cheques issued against each Project
    2.) Which cheques have been redeemed against the cheques issued
    3.) Show the total value by project unredeemed against each Project

    Example:
    Project 001
    Chq issued 1000 to 1005

    Project 002
    Chq issued 1000 to 1003

    Redeemed
    Project 001
    Chq num 1000
    Chq num 1001
    Chq num 1002


    Result
    Project Num Chq Num Issued Redeemed outstanding 001 1000 1 1 0
    001 1001 1 1 0
    001 1002 1 1 0
    001 1003 1 0 1
    001 1004 1 0 1
    001 1005 1 0 1
    002 1000 1 0 1
    002 1001 1 0 1
    002 1002 1 0 1
    002 1003 1 0 1


    The problem is there is no direct link back to the cheques issued as they are issued in a range of cheques. The range of cheques can also relate to a number of projects.
    Note: The set up of the tables cannot be changed. The program I am working on is linking to this database.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Re: Complex Query dealing with data in a range

    The word document might be easier to read(attached)
    Attached Files Attached Files

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Where is the cheque value stored?

    blindman

  4. #4
    Join Date
    Jul 2003
    Location
    Kaukapakapa, NZ
    Posts
    15

    Re: Complex Query dealing with data in a range

    Originally posted by HelpMePlease
    The word document might be easier to read(attached)
    Possible solution:

    1: Create a new table with 3 fields: Project_Num, ChqNum, Redeemed

    Redeemed can be a Y/N or text Len 1, either way a default value N

    2: Appended records to the new table from tblChqIssued for each project using for i = x to y where x = Start_Num, y = StopNum.

    This will create a table that has each project and the cheques issued against that project with a redeemed value N.

    3: Now you can link this table with tblRedeemed using Project_Num ChqNum and update Redeemed to Y.

    4: Query the new table Grouping on Project_num and

    ChqNum Count (total number of cheques issued to that project)

    Redeemed Count criteria Y (number redeemed)

    Redeemed Count criteria N (number not redeemed)

    Hope this helps

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    The cheque value is always £1 per cheque so there is no problem with the value

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Thanks for your help, However:-

    I am having trouble with expression, gives me a message = “The expression you have entered contains invalid syntax” it seems to be on the where clause.

    I have also created an expression that identifies the number of cheques issued. eg StartNum = 3001 StopNum 3025. I now have an expression that tells me that 25 where issued. Not sure if this helps but, that where im at, at the moment.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is that you want to display rows for checks that have been issued but not redeemed, but these checks do not exist as entities in your table design. There is a nifty way around this problem.

    I've found on many occasions that it is handy to have a table in the database that consists of sequential number valuse starting at zero:

    CREATE TABLE [dbo].[sequential_numbers] ([value] [int] NOT NULL ) ON [PRIMARY]
    GO

    Populate the table with enough value to cover the maximum number of checks that will be issued to any one project. You can use this table in a subquery to simulate the missing data elements (issued checks), and your report query will look like this:

    select
    Issued.Project_Num,
    Issued.ChqNum,
    1 as Issued,
    sum(case when tblRedeemed.ChqNum is null then 0 else 1 end) as Redeemed,
    sum(case when tblRedeemed.ChqNum is null then 1 else 0 end) as Outstanding

    from
    (select Project_Num, Start_Num+value as ChqNum
    from tblChqIssued
    inner join sequential_numbers on sequential_numbers.value <= (StopNum - Start_Num)) Issued

    left outer join tblRedeemed on issued.project_num = tblRedeemed.projectnum
    and issued.ChqNum = tblRedeemed.ChqNum

    group by Issued.Project_Num, Issued.ChqNum
    order by Issued.Project_Num, Issued.ChqNum

    blindman

  8. #8
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Thanks for your help - I will give this a try and let you know

Posting Permissions

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