Copy data in sheet1 using sheet 2 and Access table data.

[aman]

Hi All

I am using Excel/VBA as a frontend and Access backend.Please see the attached workbook. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.

The following formulas will be used in the select statment:

Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected

Any ideas how can this be done??

Many Thanks​

 

[aman]

Hi All, I tried to figure it out but no joy.. Can anyone please put some efforts to help me in this?? Many thanks .

 

[JHB]

Why are you using that setup, why do you not keep it all in MS-Access?
Which part is giving you problem?
Do you've some sample data in a MS-Access database, then post it!

 

[aman]

Thnks for your reply JHB. This is the requirement of the project that we should use Excel as a fronend and Access as a backend.

Because of security restrictions in my company, I can't send u the Access database attachment but I have copied the some data across Excel sheet as attached to give you an idea that how the data is stored in Access Table.

ANy questions please let me know.

Thanks

 

[JHB]

Because of security restrictions in my company, I can't send u the Access database attachment but I have copied the some data across Excel sheet as attached to give you an idea that how the data is stored in Access Table.

It is Okay, I only want some sample data.
Which column store the Queue number, (in AccessDB.xls).

Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2.

What do you mean, can you clarify?

 

[aman]

JHB, The Queue number consist of Type & Type1 & Type2. SO in Access table, there is no Queue number field but the Queue number is the combinantion of Type,Type1 and Type2 fields .

The sheet2 stores Queue Names for the corresponding Queue numbers. In Sheet1, In column K all the Queue Names are stored. Now for each Queue Name, we need t find Queue Number from Sheet2 and check in the Access table if that Type&Type1&Type2=Queue Number and if yes then find total Batches,Envelopes,Documnts and Pages and store in Sheet 1 against the corresponding Queue Names.

I hope I made it clear. ANy other questions please let me know.

Thanks

 

[JHB]

I've made an example for you, using the data you sent me.
Save both the attached database and the Excel file.
Remember to change the connection string to the path where you save the attachment.
Input the date "16-08-2012" in the userform.
I'll leave it to you to optimize the code.

 

[aman]

Thanks a lot JHB. Its a gr8 help. It worked perfectly fine now.. Many Thanks

 

[aman]

JHB, Now I want to work on the next part i.e populating data in columns Q to X. Could you please help me in this too? Many Thanks

 

[JHB]

Can't you see how to do it from the sample database and if not, what is special here?

 

[aman]

JHB, There are Time slots in the columns from Q to X. i.e 8:00,9:00,10:00,11:00.....etc.

so we need to find out percentage of batches scanned before the above time slots..

Thanks

 

[JHB]

Then run a crosstab query after the select query you have, using the same criteria.
And inserting the values in the right column using code in the same way you do for the result returning from the select query.

 

[aman]

Thanks JHB, Sorry to bother u again . I am using the following code to count number of batches done for Scantime < 08:00 but it gives me error message in the query:

strsql = "SELECT Count(BatchNo) AS CountOfBatchNo " _
  & "FROM jabberwocky " _
  & "GROUP BY ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _
  & "where ScanDate=" & J & " and ScanTime=#" & Format(ScanTime, "hh:mm:ss") & "#"
  
  rs.Open strsql, cn
  If Not rs.EOF Then
    Do
      QueueName = FindQueueNo(rs![QueueNo])
      If QueueName <> "" Then
        RowNo = FindQueueName(QueueName)
        If RowNo <> 0 Then
          ws.Range("Q" & RowNo) = rs![CountOfBatchNo]
               End If
      End If
      rs.MoveNext
    Loop Until rs.EOF
  End If

Please help me in this.

Thanks

 

[JHB]

Only for info, you're missing the QueueNo in the select list.
Then you've input a second criteria, Format(ScanTime, "hh:mm:ss"), where do you declare and set the value for the variable ScanTime?

JHB, There are Time slots in the columns from Q to X. i.e 8:00,9:00,10:00,11:00.....etc.

so we need to find out percentage of batches scanned before the above time slots..

Should I read the above like this:
If one is scanned at 7:00, one at 9:00, one at 11:00:
In 8:00 =1 (1 at 7:00), in 9:00 = 2 (1 at 7:00 + 1 at 9:00), in 10:00 = 2 (1 at 7:00 + 1 at 9:00), in 11:00 = 3 (1 at 7:00 + 1 at 9:00 + 1 at 11:00), in 12:00 = 3 (1 at 7:00 + 1 at 9:00 + 1 at 11:00) or how???
Could you show a fulfilled row in the Excel sheet, for some sample data (which you also show)?
What about batches scanned after 15:00 o'clock?
And is it only for batches scanned and not something more afterwards?

 

[aman]

Hi JHB, I am writing the following code to display the percentage of total bathes scanned before 8:00,9:00,10:00..... etc

If one is scanned at 7:00, one at 9:00, one at 11:00:
In 8:00 =1 (1 at 7:00), in 9:00 = 2 (1 at 7:00 + 1 at 9:00), in 10:00 = 2 (1 at 7:00 + 1 at 9:00), in 11:00 = 3 (1 at 7:00 + 1 at 9:00 + 1 at 11:00), in 12:00 = 3 (1 at 7:00 + 1 at 9:00 + 1 at 11:00)

Yes above is right apart from we need to display the percentage of bacthes scanned and not the count of total batches scanned. This is what I want. There won't be anything after 15:00 as batches are scanned before this time only.

We need to change the below code so that it displays the percentage of total batches scanned before each time slot.

Private Sub CommandButton1_Click()
Call func1("08:00:00", "Q")
  Call func1("09:00:00", "R")
   Call func1("10:00:00", "S")
    Call func1("11:00:00", "T")
     Call func1("12:00:00", "U")
      Call func1("13:00:00", "V")
       Call func1("14:00:00", "W")
        Call func1("15:00:00", "X")
End Sub
 
Function func1(a As Date, b As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim J As String
  Dim RowNo As Long
  Dim QueueName As String
    ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\workqueue.mdb;"
 
Set rs = New ADODB.Recordset
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
 J = ComboBox3.Value & ComboBox2.Value & ComboBox1.Value
 strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _
  & "FROM jabberwocky " _
  & "group By ScanDate,ScanTime, [Type] & Format([Type1],'00') & Format([Type2],'00') " _
  & "HAVING ScanDate=" & J & " and Scantime < " & Format(a, "\#hh\:mm\:ss\#")
 
  rs.Open strsql, cn
  If Not rs.EOF Then
    Do
      QueueName = FindQueueNo(rs![QueueNo])
      If QueueName <> "" Then
        RowNo = FindQueueName(QueueName)
        If RowNo <> 0 Then
             ws.Range(b & RowNo) = rs![CountofBatchNo]
        End If
      End If
      rs.MoveNext
    Loop Until rs.EOF
  End If
End Function

Thanks for your help so far.

 

[JHB]

Try the attached file.
As mention before, you should use a Cross-tab query, have you even look at it?

There won't be anything after 15:00 as batches are scanned before this time only.

I the sample data, there are some scanned at 23:23:23 o'clock.

 

[aman]

Hi JHB, Its giving me data type mismatch in criteria expression error in the line in Red Font in the following code :

 strsql = "TRANSFORM Count(jabberwocky.BatchNo) AS CountOfBatchNo " _
  & "SELECT [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo, Count(jabberwocky.ScanDate) AS Total " _
  & "FROM jabberwocky " _
  & "WHERE ScanDate='" & J & "' " _
  & "GROUP BY [Type] & Format([Type1],'00') & Format([Type2],'00') " _
  & "PIVOT Format([ScanHour],'00') In (08, 09,10,11,12,13,14,15);"
  
[COLOR=red]  rs.Open strsql, cn
[/COLOR]  If Not rs.EOF Then
    Do
      QueueName = FindQueueNo(rs![QueueNo])
      If QueueName <> "" Then
        RowNo = FindQueueName(QueueName)
        If RowNo <> 0 Then
          For x = rs.Fields.Count To 3 Step -1
            'MsgBox (rs.Fields(x - 1).Name)
            RememberCountOfBatch = RememberCountOfBatch + IIf(IsNull(rs.Fields(x - 1)), 0, rs.Fields(x - 1))
            ws.Range(Cells(RowNo, 24 - (10 - x)), Cells(RowNo, 24 - (10 - x))) = (rs![Total] - RememberCountOfBatch) / rs![Total]
          Next x
        End If
      End If
      rs.MoveNext
    Loop Until rs.EOF
  End If

 

[JHB]

Not by me, show the whole procedure/sub.

Another way for testing it is to use Debug.Print and run the output from the Immediate window, in MS-Access.
Show the output also.

 

[aman]

It gives error at
rs.open strsql,cn

in the immediate window, its showing as below:

TRANSFORM Count(jabberwocky.BatchNo) AS CountOfBatchNo SELECT [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo, Count(jabberwocky.ScanDate) AS Total FROM jabberwocky WHERE ScanDate='20140730' GROUP BY [Type] & Format([Type1],'00') & Format([Type2],'00') PIVOT Format([ScanHour],'00') In (08, 09,10,11,12,13,14,15);

 

[JHB]

Put the output from the Immediate window into a new query in the MS-Database, then it is easier for you to see what is wrong.

I don't have your database, so I can't tell what is wrong, then as mention before it runs okay by me, in the database created from the data you gave me.
Maybe some of your fields type are numbers, by me all Text.