Results 1 to 4 of 4
  1. #1
    Vasha is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    3

    Find which interval a record is in

    Hi -- I have a table Issues containing records for magazine issues each with an IssueNumber, and another table Volumes which contains VolumeNumber and StartIssue specifying the first issue of each volume: vol. 1 starts with issue 1, 2 starts with 27, etc. (Start numbers are specified individually because the volumes contain variable numbers of issues.)

    So then, what would be the expression for a query column that, for each issue number, finds which volume it belongs to?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    wouldnt you have the 2 tables: tPub, tIssues
    tPub table:
    ------------------
    PubID, PubName,etc..
    1, LIFE,
    2, VOGUE,

    tIssues table:
    PubID, Issue, Vol, etc
    1, 1, 1,...
    1, 2, 1,...

    the volumn ends with the Max VOL #.

  3. #3
    Vasha is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    3
    The reason I have it organized the way it is, is because there's only one magazine, with issues numbered 1 through 279. They are also grouped into volumes. So the minimum amount of data duplication is one record for each issue, and in a separate table, information about the volumes (which includes the first issue in each volume as I said plus other data related to how each volume was published in book form, separately from the publication as individual issues). From the StartIssue I should be able to calculate which volume each issue is in, that's what the query I'm asking about should do -- that way I don't have to explicitly add a volume-number field to each issue record.
    Last edited by Vasha; 02-12-2019 at 09:26 AM. Reason: added anoter sentence

  4. #4
    Vasha is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    3
    Update: no longer needed for this database because I now have the two tables linked via the volume number (for only 279 records it's not difficult to do by hand). But I'm still interested in the theoretical answer to the question.

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

Similar Threads

  1. Replies: 6
    Last Post: 04-16-2018, 09:15 PM
  2. Find Record with date before Current Record
    By mrmims in forum Queries
    Replies: 1
    Last Post: 10-01-2015, 08:10 AM
  3. Replies: 1
    Last Post: 06-23-2014, 07:21 AM
  4. Replies: 4
    Last Post: 04-01-2014, 02:33 PM
  5. Interval Reporting
    By mlopez in forum Queries
    Replies: 3
    Last Post: 07-16-2012, 05:48 PM

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