Results 1 to 13 of 13
  1. #1
    akellaavss is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2013
    Posts
    5

    serial number

    I have the following table and i need to generate the report from the table which includes serial number also


    Table:


    x 12
    y 14
    z 20
    a 36
    y 16
    y 54
    x 45

    Report shall look like the following

    Sl.No Name Marks
    1 x 12
    45
    2 y 14
    16
    54
    3 z 20
    4 a 36

    I am able to hide the duplicate values in name, However, the serial no. column needs to be adjusted as shown above.

    pl. help in this regard.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,752
    Adjusted how? Where does the 4 of "4 a 36" come from? Why do 45, 16, 54 just drop the alphas? What are the rules for 'adjusting'? How many different 'adjustments' are possible?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    akellaavss is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2013
    Posts
    5
    Sorry for not clarity in the question. I am enclosing the database. I need in the report the serial number shall be available based on the name only but not for all records.

    Hope my requirement is clear
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,752
    Provides no help at all.

    Nothing in the db looks like the output example in your post. Still have not addressed the questions I asked.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,439
    I suggest you tell readers WHAT you are trying to do in simple English.
    What are the things involved and what are the rules?

    Before readers can give advice, they have to understand what you are trying to accomplish.
    Good luck with your project.

  6. #6
    akellaavss is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2013
    Posts
    5
    I am really sorry, Since I am posting the question first time, I could not give the clarity. I hope the following tables explain my requirement.

    My table contains the following data
    Table1
    Date Gate pass Name qty
    01-06-2015 451 abc 23
    01-06-2015 451 abc 56
    01-06-2015 452 xyz 15
    01-06-2015 453 efg 25
    02-06-2015 454 abc 25
    02-06-2015 455 xyz 20
    02-06-2015 455 xyz 25


    I Want the report to look like below.

    Sl No Gate pass Name qty
    Gate pass issued on : 01.06.2015
    1 451 abc 23
    56
    2 452 xyz 15
    3 453 efg 25
    Gate pass issued on : 02.06.2015
    1 454 abc 25
    2 455 xyz 20
    25

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,439
    Not really.

    Why is it so difficult to tell us what you are trying to do in simple terms?

  8. #8
    akellaavss is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2013
    Posts
    5
    I am able to hide the duplicate values of gate pass no. and name in the report. However, the serial number shall be incremental by gate pass number but not on every record. (i.e for the second record in my report above, only quantity is visible, I set the property of name and gatepass to hide duplicates. however, i am not able to get the serial number as desired above.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,439

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,752
    You want a sequence ID for each gate pass number and you want the sequence to start over for each date?

    This would not be simple to do in query or on form.

    A textbox on report has RunningSum property which can be used to generate a sequence ID. Use report Sorting & Grouping features to set groups for IssueDate and GatePass. Textbox in GatePass group header calculates the incrementing ID with ControlSource expression of =1 and RunningSum set to OverGroup.

    Put Name and Qty fields in report Detail section.

    Name is a reserved word. Should not use reserved words as object or field names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    akellaavss is offline Novice
    Windows Vista Access 2007
    Join Date
    Oct 2013
    Posts
    5
    Thanks for the reply. This I have tried and got it. However, the report print looks like stepped because of gatepass group header. Is there any other way of writing code for this.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,752
    Other options:

    1. save the sequence value in record when it is committed to table - this is generating a custom unique identifier - common topic in forum

    2. VBA code to populate field for existing records

    3. review: http://allenbrowne.com/ranking.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    However, the report print looks like stepped because of gatepass group header
    What do you mean by this?

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

Similar Threads

  1. Replies: 6
    Last Post: 02-10-2014, 01:04 PM
  2. Increment part of a serial number
    By swavemeisterg in forum Forms
    Replies: 10
    Last Post: 04-15-2013, 01:59 PM
  3. Replies: 6
    Last Post: 03-21-2012, 07:13 PM
  4. Processor Serial Number
    By Azeez_Andaman in forum Programming
    Replies: 2
    Last Post: 08-16-2011, 11:33 AM
  5. Help On Creating Automatic of a Serial Number
    By lm_lopes in forum Programming
    Replies: 4
    Last Post: 03-10-2010, 06:47 AM

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