Seeting up Query in acces 2010

[AdamT]

Good afternoon,

Good afternoon,
I have a table which contains the following columns:

A2ID Revision Cage Code A2DrawingNumber Item Qty

I am able to load these records to my list box with no problem. These records are not similar, but they one thing in common the “Revision”. For example the following records are Revision “B”
A2ID Rev Cage
8 B 53711 PL7447550 2 1
9 B 53711 PL7447550 3 1
10 B 53711 PL7447550 4 2
11 B 53711 PL7447550 5 1

When I load these records to List Box I would like to display only one record within List Box, instead of 4 records. How would I set my query?
Please see attached zip file.

V/r
Adam

[June7]

Could not include A2ID, Item in query at all. Include Qty only if sum it.

SELECT DISTINCT Revision, [Cage Code], A2DrawingNumber FROM tablename;

or

SELECT Revision, [Cage Code], A2DrawingNumber, Sum([Qty]) AS SumQ FROM tablename GROUP BY Revision, [Cage Code], A2DrawingNumber;


What purpose is listbox? Is it useful without the A2ID field?


BTW, advise no spaces or special characters in names.

[Dal Jeanis]

Adam -

1) if you put the word CODE in front of your words and the word /CODE after them, in square brackets [], the forum won't squish your layout.

2) You have your choice of what to put in the box. If you use the words GROUP BY at the end of your query, as June7's second suggestion says, then you will only get one record per each combination of the fields that you grouped the data by. But that also means you can only load fields that are either (A) in your GROUP BY list, or (B) calculated by an aggregate function. The most commonly used of those functions are SUM, AVG, COUNT, MAX, MIN, and FIRST. You can look here for descriptions of them http://office.microsoft.com/en-us/ac...001032172.aspx.

[June7]

As Dal suggested, use CODE tags to keep fields aligned. Or go to the Advanced post editor for more tools, such as building a table.

[AdamT]

Good morning,
Thank you for helping me with the issue of query and list box. This time I have provided you the actual database. The purpose of the list box is when the user click or double click on revision, then another form open and list all associated part lists.

If I have 5 records which they share the same revision, then ideally I want “list box” to display one record (i.e. one Revision, instead of 5 revision). Please see attached zip file
Thank you again for your valuable time.

V/r
Adam

[June7]

Did you attempt either of the suggested queries for listbox RowSource?

[AdamT]

Dear June7,

Indeed I did attempt and use your suggestion for setting up the query for listbox. Still, the list box dispaly the all the records. I purposely submitted the database so you can look at the table and query.

V/r
Adam

[Dal Jeanis]

You are overcomplicating your life. You don't need six buttons, you need three. I killed three buttons, renamed the other three to shorter names, and created a form-level variable "intSortSelected" to keep track of the selection. When the user clicks a button, if that field is already set to ascending, it will switch to descending. Otherwise, it will switch to ascending.

The SELECT CASE code for the SQL isn't elegant, but it does the job.

Option Compare Database
Option Explicit
Dim intSortSelected As Integer

Private Function basOrderby() As Integer
Dim strSQL As String
'Clear captions of asc and desc symbols
    Me!cmdRevision.Caption = "Order by Revision"
    Me!cmdCageCode.Caption = "Order by Cage Code"
    Me!cmdA2CPCI.Caption = "Order by A2 CPCI Chassis"
Select Case intSortSelected
Case 1
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "ORDER BY Revision ASC, CageCode ASC, A2DrawingNumber ASC "
    Me!cmdRevision.Caption = "^ Order by Revision ^"
Case 2
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "ORDER BY Revision DESC, CageCode ASC, A2DrawingNumber ASC "
    Me!cmdRevision.Caption = "v Order by Revision v"
Case 3
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY CageCode, Revision, A2DrawingNumber "
    strSQL = strSQL & "ORDER BY CageCode ASC, Revision ASC, A2DrawingNumber ASC "
    Me!cmdCageCode.Caption = "^ Order by Cage Code ^"
Case 4
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY CageCode, Revision, A2DrawingNumber "
    strSQL = strSQL & "ORDER BY CageCode DESC, Revision ASC, A2DrawingNumber ASC "
    Me!cmdCageCode.Caption = "v Order by Cage Code v"
Case 5
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY A2DrawingNumber, Revision, CageCode "
    strSQL = strSQL & "ORDER BY A2DrawingNumber ASC, Revision ASC, CageCode ASC "
    Me!cmdA2CPCI.Caption = "^ Order by A2 CPCI Chassis ^"
   
Case 6
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY A2DrawingNumber, Revision, CageCode "
    strSQL = strSQL & "ORDER BY A2DrawingNumber DESC, Revision ASC, CageCode ASC "
    Me!cmdA2CPCI.Caption = "v Order by A2 CPCI Chassis v"
   
Case Else
    strSQL = "SELECT Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "FROM A2CPCIChassisPL7447550 "
    strSQL = strSQL & "GROUP BY Revision, CageCode, A2DrawingNumber "
    strSQL = strSQL & "ORDER BY Revision ASC, CageCode ASC, A2DrawingNumber ASC "
    Me!cmdRevision.Caption = "^ Order by Revision ^"
End Select

'Set row source for list box
    
    Me!lstSearch.RowSource = strSQL
    Me!lstSearch.Requery
    Me!lstSearch.SetFocus
    
End Function


Private Sub cmdRevision_Click()
Dim response As Integer
    
    If intSortSelected = 1 Then
       intSortSelected = 2
    Else
       intSortSelected = 1
    End If
    response = basOrderby()
    
End Sub


Private Sub cmdCageCode_Click()
Dim response As Integer
    
    If intSortSelected = 3 Then
       intSortSelected = 4
    Else
       intSortSelected = 3
    End If
    response = basOrderby()
End Sub

Private Sub cmdA2CPCI_Click()
Dim response As Integer
    
    If intSortSelected = 5 Then
       intSortSelected = 6
    Else
       intSortSelected = 5
    End If

    response = basOrderby()
    
End Sub

By the way, I killed the ID field from the SQL, because as a unique primary key it makes every row distinct.

That means you have to delete the first column width from the listbox.

You should also delete the default Row Source in the listbox and instead call basOrderBy() in the form's Load event.

[AdamT]

Thank you very much for your quick response. I will incorporate your suggestion today. Happy Fourth of July.

V/r
Adam

[Dal Jeanis]

You too. If you got everything you needed, please mark the thread "solved". Top of Page, under "Thread Tools".