By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,600 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,600 IT Pros & Developers. It's quick & easy.

Aggregating Data - Between 2 Semi-Related Tables

P: 9
I have an interesting situation.

In one table (we'll call it Tabel1) I have requests. Each request can contain multiple codes

In the other table (Tabel2) I have a bunch of results, with unique codes (the codes in table 1) and subsequent data.

Example:
Tabel1 -
Tracking Number(PK): 1234
Codes: CODE1, CODE2, CODE3
Tabel2 -
CODE(PK): CODE1
COUNT1: 1,000
DATA1: $5.00
DATA2: $10.00

CODE(PK): CODE2
COUNT1: 3,000
DATA1: $6.00
DATA2: $12.00

CODE(PK): CODE3
COUNT1: 5,000
DATA1: $7.00
DATA2: $14.00


What I need to do is for each request aggregate the corresponding data in table2 (i.e. add COUNT1; avg DATA1; avg DATA2)

I would like to have the CODES field in Tabel1 be separated by a comma; but if this is not possible, then have separate inputs for each code (i.e. CODE1; CODE2; CODE3)


I have tried creating seperate queries for each code, then combining the queries, but this only yields results when a tracking number has each code filled in.


Thank you in advance!
Jan 16 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
I have an interesting situation.

In one table (we'll call it Tabel1) I have requests. Each request can contain multiple codes

In the other table (Tabel2) I have a bunch of results, with unique codes (the codes in table 1) and subsequent data.

Example:
Tabel1 -
Tracking Number(PK): 1234
Codes: CODE1, CODE2, CODE3
Tabel2 -
CODE(PK): CODE1
COUNT1: 1,000
DATA1: $5.00
DATA2: $10.00

CODE(PK): CODE2
COUNT1: 3,000
DATA1: $6.00
DATA2: $12.00

CODE(PK): CODE3
COUNT1: 5,000
DATA1: $7.00
DATA2: $14.00


What I need to do is for each request aggregate the corresponding data in table2 (i.e. add COUNT1; avg DATA1; avg DATA2)

I would like to have the CODES field in Tabel1 be separated by a comma; but if this is not possible, then have separate inputs for each code (i.e. CODE1; CODE2; CODE3)


I have tried creating seperate queries for each code, then combining the queries, but this only yields results when a tracking number has each code filled in.


Thank you in advance!
First Off this can be done in code, somewhat easily. But I'm assuming some of the problem comes when some or all of the items done have any codes?

The main design faul that you have going on here is that you have have vertical design of a list have the horizontal values from a verical list without having some kind of code to figure out what you're trying to do.

So here it goes,

You're going to need to create three functions to return each item that you're speaking about, with a little error handling in case there isn't any data in any of the fields or some of the fields.

You need to go into your database windows and select Modules

Once you're in there click on the NEW button at the top of the window.

in the code window copy and paste the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function GetCodeCount(Optional CodeField1 As String, Optional CodeField2 As String, Optional CodeFile3 As String) As Integer
  3.  
  4.           Dim MyDB As DAO.Database
  5.           Dim MyRS1 As DAO.Recordset
  6.           Dim MyRS2 As DAO.Recordset
  7.           Dim MyRS3 As DAO.Recordset
  8.           Set MyDB = CurrentDb()
  9.  
  10.          If Len(CodeField1) < 1 And Len(CodeField2) < 1 And Len(CodeField3) < 1 Then
  11.                 GetCodeCount = 0
  12.          Else
  13.                 Set MyRS1 = MyDB.OpenRecordset("SELECT Count1 FROM [TABLENAME] WHERE Code = '" & CodeField1 & "'", dbOpenSnapshot)
  14.                 Set MyRS2 = MyDB.OpenRecordset("SELECT Count1 FROM [TABLENAME] WHERE Code = '" & CodeField2 & "'", dbOpenSnapshot)
  15.                 Set MyRS3 = MyDB.OpenRecordset("SELECT Count1 FROM [TABLENAME] WHERE code = '" & CodeField3 & "'", dbOpenSnapshot)
  16.                 GetCodeCount = Nz(MyRS1!Count1, 0) + Nz(MyRS2!Count1, 0) + Nz(MyRS3!Count1, 0)
  17.          End If
  18.          MyRS3.Close
  19.          MyRS2.Close
  20.          MyRS1.Close
  21.          MyDB.Close
  22.  
  23.          Set MyRS3 = Nothing
  24.          Set MyRS2 = Nothing
  25.          Set MyRS1 = Nothing
  26.  
  27. End Function
  28.  
  29. Function GetCodeAvg1(Optional CodeField1 As String, Optional CodeField2 As String, Optional CodeField3 As String) As Single
  30.     Dim MyDB As DAO.Database
  31.     Dim MyRS1 As DAO.Recordset
  32.     Dim MyRS2 As DAO.Recordset
  33.     Dim MyRS3 As DAO.Recordset
  34.     On Error GoTo Err_GetCodeAvg1
  35.     Set MyDB = CurrentDb()
  36.     Set MyRS1 = MyDB.OpenRecordset("SELECT Data1 FROM [TABLENAME] WHERE Code = '" & CodeField1 & "'", dbOpenSnapshot)
  37.     Set MyRS2 = MyDB.OpenRecordset("SELECT Data1 FROM [TABLENAME] WHERE Code = '" & CodeField2 & "'", dbOpenSnapshot)
  38.     Set MyRS3 = MyDB.OpenRecordset("SELECT Data1 FROM [TABLENAME] WHERE Code = '" & CodeField3 & "'", dbOpenSnapshot)
  39.  
  40.     GetCodeAvg1 = (Nz(MyRS1!Data1, 0) + Nz(MyRS2!Data1, 0) + Nz(MyRS3!Data1, 0)) / 3
  41.     Exit Function
  42. Err_GetCodeAvg1:
  43.     GetCodeAvg1 = 0
  44.  
  45. End Function
  46.  
  47. Function GetCodeAvg2(Optional CodeField1 As String, Optional CodeField2 As String, Optional CodeField3 As String) As Single
  48.     Dim MyDB As DAO.Database
  49.     Dim MyRS1 As DAO.Recordset
  50.     Dim MyRS2 As DAO.Recordset
  51.     Dim MyRS3 As DAO.Recordset
  52.     On Error GoTo Err_GetCodeAvg2
  53.     Set MyDB = CurrentDb()
  54.     Set MyRS1 = MyDB.OpenRecordset("SELECT Data2 FROM [TABLENAME] WHERE Code = '" & CodeField1 & "'", dbOpenSnapshot)
  55.     Set MyRS2 = MyDB.OpenRecordset("SELECT Data2 FROM [TABLENAME] WHERE Code = '" & CodeField2 & "'", dbOpenSnapshot)
  56.     Set MyRS3 = MyDB.OpenRecordset("SELECT Data2 FROM [TABLENAME] WHERE Code = '" & CodeField3 & "'", dbOpenSnapshot)
  57.  
  58.     GetCodeAvg2 = (Nz(MyRS1!Data2, 0) + Nz(MyRS2!Data2, 0) + Nz(MyRS3!Data2, 0)) / 3
  59.     Exit Function
  60. Err_GetCodeAvg2:
  61.     GetCodeAvg2 = 0
  62.  
  63. End Function
  64.  
Make sure to reference the DAO library in the Reference Option under tools. You need to select Microsoft DAO 3.x depending on what version of access you have installed.

I hope that helps,

Joe P.
Jan 22 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.