Results 1 to 5 of 5
  1. #1
    Ron Godbout is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    5

    Select Distinct

    I have a table [JobItems] that contains all products we have sold a customer (or are quoting a customer). In this table I have the following fields (among many more) [Description], [ItemID] that is a text field and contains the unique ID from a different table [Inventory]. I also have the field [ProjectID], which would be a unique number (again in a text format) that keeps track of the particular Customer Project.

    We may have many items in the table [JobItems] that have the same ID [ItemID] for a particular project (ie we may have 10 products with the same ID for 1 project with different lengths & quantities).

    What I am trying to do is formulate a routine that would loop thru all the items for a particular job and determine:


    • The combined length and total cost of all items with the same [ProjectID] and [ItemID]. the formulas for total length = quantity * length, and for total cost = total length * cost. I would also like the [Description] of this item
    • Then loop thru the table for any other items (different [ItemID] and provide me with the same information


    To get the end result 'For job [ProjectID] you have (15) of the same items [Description] with a combined total length of (??) and a combined cost of (??) and you have (10) of the same items etc.

    I believe this would work like a report with grouping, however cannot find the syntax in the reports to duplicate. I have done a lot of research on this routine, and believe that I should be using Select, From and Group By, with a Distinct clause however, I keep running into errors - ie I have tried the following code:
    select jobitems.itemID, sum(QTY) as TotalLength, count(distinct ItemID as N from JobItems group by ItemId

    And I keep getting a Compile error: Expected: Case and it highlights the [JobItems.ItemID]

    Can anyone please help me out or at least get me started - much appreciated

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Sounds like your using the "Select" incorrectly. "Select", when used in this context, i.e. as the first word of a select query, is not a part of VBA - it is part of SQL, and it is always going to be a part of a string used in defining a query or recordset.

    Since you want to "loop through all items...", you probably want to use a recordset, with Skeleton code something like this:

    Code:
    Dim rst as recordset
    Dim SQL as string
    '
    ' Put the SQL Select statement into a string variable
    '
    SQL = "Select....."   
    '
    ' Open the recordset
    '
    set rst = CurrentDB.OpenRecordset(SQL)
    '
    ' Step through all the records in the Recordset
    '
    While not rst.EOF
      '
      '  Do whatever you need
      '
      rst.Movemext
    Wend
    rst.Close
    One method of getting the proper SQL is to develop a query gives the results the results you want, then copy the query SQL to your VBA routine.

    However, from looking at your requirements, you might not need VBA at all - a properly designed Totals query should be able to meet your requirements - Group By on ProjectID and ItemID should do, with other columns using Count or Sum to aggregate your values.
    Last edited by John_G; 05-17-2016 at 04:39 PM. Reason: to clarify the comment

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ron,

    John has given the details for creating and looping thru a record set. I think it would be helpful to readers if you showed us the tables and relationships used in your database. Inventory is not a trivial topic and has been discussed in various forums. Getting your tables designed/normalized to meet your business requirements is critical to a successful database.

    Good luck with your project.

  4. #4
    Ron Godbout is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    5
    Thanks John

    Have not had the time to work with this, however your reply has started me in the right direction. Not sure how to use the Sum or Count functions in a Totals query, however will spend some time working on this.

    Greatly appreciated

  5. #5
    Ron Godbout is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2016
    Posts
    5

    Thread Solved

    Again - Thanks John

    Using your directions, I have written the code in needed to complete what I wanted to do.

    For Anyone interested, below is the code that I wrote:

    Code:
    Private Sub cmdGenerate_Click()

    'step thru records in table JobItems, assign Markups and determine
    'Selling Price based on the markup (Qty * QuoteSpan * Cost)
    'if MasterID = "01" (HTS beam) then we must determine the aggregate length
    'of all equal series and depths (ItemID)to detemine the MarkUp
    'Else find the MarkUp of each product in table InMarkUp
    'To determine the aggregate length, design a Group By query similar to [JobItems Query1]
    Dim rstSQL As Recordset
    Dim rstTable As Recordset
    Dim SQL As String

    'place SQL statement into string
    SQL = "SELECT JobItems.[ItemId], Sum([QuoteSpan]*[QTY]) AS TotalLf FROM JobItems GROUP BY JobItems.[ItemId];"

    'open the SQL recordset
    Set rstSQL = CurrentDb.OpenRecordset(SQL)
    rstSQL.MoveFirst

    'set thru records
    While Not rstSQL.EOF
    'write ItemID to me.ItemID
    Me.ItemID = rstSQL!ItemID

    'determine if group of items = HTS Beams (MasterID = 01)
    If Left(rstSQL!ItemID, 2) = "01" Then 'is HTS Beam
    'determine aggregate length
    If rstSQL!totallf > 200 Then
    Me.MarkUp = ".25"
    Else: Me.MarkUp = ".35"
    End If
    MsgBox ""
    Else
    Me.MarkUp = ".50"
    MsgBox "not beam"
    End If

    'write markups to table JobItems
    'must step thru all grouped records
    Set rstTable = CurrentDb.OpenRecordset("JobItems")
    rstTable.MoveFirst

    'step thru records
    While Not rstTable.EOF
    If rstTable!ItemID = Me.ItemID Then 'update markup with current markup
    rstTable.Edit
    rstTable!MarkUp = Me.MarkUp
    rstTable.Update
    End If

    rstTable.MoveNext
    Wend

    rstSQL.MoveNext
    Wend
    End Sub
    Note sure how to insert the above code into a 'code box' - sorry

    Again Thanks

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

Similar Threads

  1. How to do Grouping with Select Distinct
    By accessmatt in forum Queries
    Replies: 5
    Last Post: 01-22-2015, 03:24 PM
  2. Select Distinct Help
    By fionny in forum Queries
    Replies: 2
    Last Post: 06-28-2013, 08:24 AM
  3. Select distinct
    By Paul-NYS in forum Access
    Replies: 1
    Last Post: 07-09-2012, 09:03 AM
  4. SELECT DISTINCT or equivalent
    By kpo in forum Programming
    Replies: 13
    Last Post: 05-20-2012, 01:45 PM
  5. Replies: 8
    Last Post: 04-21-2011, 05:29 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