Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Unanswered: Create Incremental Number Field in Query

    Hello everyone and happy new year!

    Just joined the forum so please go easy on me if my post is poorly assembled

    I have an MS Access 2010 query which pulls data from a table without a primary key. The data in this table is created by way of an imported CSV file, of which I have little or no control over, other than exporting it and inserting into Access.

    The table consists of 7 fields/columns:
    • Item History ID
    • Item ID
    • Description
    • Heading
    • Username
    • Created
    • Updated


    My query is designed to only show 6/7 fields. My SQL code below:

    Code:
    SELECT tbl_History.[Item ID], tbl_History.Heading, tbl_History.Description, tbl_History.Username, tbl_History.Updated, Mid([Description],34,300) AS History
    FROM tbl_History
    WHERE (((tbl_History.Heading)="Status Change"))
    ORDER BY tbl_History.[Item ID], tbl_History.Updated;
    The records from this table are displayed in a sub-report, nested within my another report. The sub-report rows are sorted by 'Item ID' 1st and then by 'Updated'. For clarity, the rows in the table are from an audit table and as a result they are grouped by the Item ID (e.g. Each product (Item ID) can appear more than once and occurs every time a 'Status update' occurs for it.

    What I am trying to do is count the number of days between the 'Updated' date of the previous record and the next row, with the intention of highlighting how many days it took to change the status. After much Google searching it appears that this is quite hard to do, especially when there is no unique ID for each row in the table. To clarify, none of the above fields in 'tbl_history' are unique. So I was thinking, perhaps if I created an auto-increment field in the query (as a temporary value) it might help me to calculate the date using this unique value.

    I know some people may suggest adding a primary key to the table itself but the data in the tables is overwritten weekly and is configured to auto-update/is linked to the CSV file.

    I know the basics of creating a new field in the query, using the 'Build Expression' method, but my lack of knowledge is hampering my efforts and as I am not well versed with any programming/coding I have been fumbling my way through this, so apologies if the above info is vague.

    Anyway, a million thanks in advance for any help, it's greatly appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if they are displayed in a sub report then I'd suggest you do the numbering in the report itself using some VBA

    in the sub report create a textbox control of the appropriate size in the detail section, give it a came, say txt_ItemNo

    in the sub reports code module.
    declare a global variable, called say ItemNo, eg:-
    Code:
    Dim ItemNo as integer 'contains the sequential number used in blah di blah
    create a header for whatever the grouping is (ie whenever you want the counter to reset, and in that group header
    Code:
    ItemNo = 1 'set our counter to 1 on start of a new group
    in the sub reports detail section
    Code:
    ItemNo = ItemNo + 1
    txt_ItemNo.text =format(ItemNo,"#,000") 'apply the format required for the number
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2016
    Posts
    2
    Quote Originally Posted by healdem View Post
    well if they are displayed in a sub report then I'd suggest you do the numbering in the report itself using some VBA

    in the sub report create a textbox control of the appropriate size in the detail section, give it a came, say txt_ItemNo

    in the sub reports code module.
    declare a global variable, called say ItemNo, eg:-
    Code:
    Dim ItemNo as integer 'contains the sequential number used in blah di blah
    create a header for whatever the grouping is (ie whenever you want the counter to reset, and in that group header
    Code:
    ItemNo = 1 'set our counter to 1 on start of a new group
    in the sub reports detail section
    Code:
    ItemNo = ItemNo + 1
    txt_ItemNo.text =format(ItemNo,"#,000") 'apply the format required for the number
    Hi Healdem and thank you for your reply. However, you lost me at "declare a global variable"

    Sorry, I am really new to this and may be going too far outside my understanding to make use of your feedback. I am prepared to persevere however!
    So, can we take this a little more slowly?
    I have opened the VB editor and navigated to my sub report (under Microsoft Access Class Objects)
    I have inserted
    Code:
    Dim ItemNo As Integer
    below "Option Compare Database"
    I have closed this with "End Sub"
    Hopefully all good so far?

    Now, when you say "create a header for whatever the grouping is (ie whenever you want the counter to reset, and in that group header" - I am not entirely sure I follow. Are you saying that in Design View I should insert a 'Group'? But I don't know what you mean by "in that group header
    Code:
    ItemNo = 1 'set our counter to 1 on start of a new group
    ". I understand I need to insert some code, but I don't know how

    I think that where I am struggling is where to enter the code each time. Do I always add this to the VB Code screen?

    You then go on to mention another code insert for the detail section, but again I am unsure how to go about this.

    Thanks again for your help, it's greatly appreciated!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK lets start from the beginning
    Most people only think Access forms and reports are done solely using the GUI designers, palcing controls on a form and so opn. but there is whole development environment lurking behind those form / report designers. think of that environment as a series of hooks which you can attach code to do stuff Microsoft didn't do (didnt' think of it) or couldn't do (too complex for a generalised model), or couldn't be expected to do (things that are specific to your application)

    in the form designer
    place a text box control in the relevant section (I'm guessing it the detail section), call it say txtItemNo, leave its datasource blank
    create a report grouping (you will need a header, and you may need a footer if say you want to count the number of items)
    .. this grouping provides a hook in the code window that allows you to do stuff using code.

    switch to code view
    declare your global variable
    Code:
    Dim ItemNo as integer 'holds the item number of this itme in a group
    , its a global variable because its globally visible to all sub routines, functions inside your application. if you choose to make the variable public its also globally visible to all forms / reports that create this object but thats another story. if it was declared inside a function it would only be available to that function. as we want to know what the value is over several rows it MUST be declared globally so it can be manipulated in more than one function
    when writing code ALWAYS make certain the (ore one of the) first line(s) is OPTION EXPLICIT which forces variables to be decalred BEFORE first use, this is important for new developers as its all to easy to make a typo and the oevrly forgiving Access environment will try and recover and not fail... leading to developers tearing their hair out 'knowing' they are using a variable called say 'myvar', whereas they'd actuially called it myvariable elsewhere. VBA doesn't care about capitalisiation, in fact with autosense in the code editor I tend to declare variabel as CamelCase eg MyVar or ProdSerialNo but type in lower case. as the autosense part of the editor detects a variable it will adjust the capitalistion to match the declaration. so as you type you get immediate visual feedback if you have typed it correctly.....

    in the group header zero that counter, which runs when whatrver change in grouping occurred (say its product types)...
    look for the group header function(s) in the code designer
    Code:
    ItemNo = 0 'zero our itemnumber variable on change of group
    in the detail on format function (which runs when the row is placed on report)
    incrment our itemNo, and then palkce the current value in the text box control
    Code:
    ItemNo = ItemNo + 1
    txtItemNo.Value = ItemNo
    if you wanted to expand this to include a summary (group footer) all you'd need to do is add a group footer in the report designer
    and in that footer add a control and set its row/datasource to be count([thenameofacontrolhere])
    ...or place some code in the group footer on format event that assigns the value of ItemNo to another control.

    These code hooks provide a means of doing fine tuning of Access form or reports. think of it as a variant of Pareto's law (where trhe GUI designers can caover 80% of the grunt work, leaving the remainign 20% down to you) ... the standard GUI designer can do most of the work for you but the fine tuning often has to be doen using code. its what adds a final polish to Access reports
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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