Match or not

[Sully38]

I have a table which contains 70 records with pct values they total 100%. I have another file that contains currency values. I need to run the currency file through the pct file and create 70 records multiplying the currency by the pct. There is no matching fields to hit on or match on. Is this possible within a query or does this require a program to be written. Any help is appreciated.

 

[mdmilner]

It depends on what you need the output to look like.

SELECT pctTable.a, *[currTable].[mycost] AS CostByItem
FROM pctTable, currTable;

Real simple SQL query where I used generic field names.
I used 3 actual dollar values to be divided into categories, and 4 categories - which produced 12 unique entries as a response, one per math calculation, which is probably not what you need.

Ordinarily I'd suggest opening two recordset objects, opening the currTable first, read the $$ value, then walk thru the entire table for the pctTable and then save the values to an array.

You could also use the above SQL, or something simlar, and walk thru the recordset once to produce an answer.

Sub AddItUp()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
Dim asnVal(4) As Single

Set dbs= CurrentDb()
strSQL = "SELECT dividebyitem.a, *[dollarcost].[mycost] AS CostByItem
FROM dividebyitem, dollarcost"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  Do Until rs.EOF
    ' Assumes the value of field a is numeric 1-4 for test
    ' This saves each of the $$ values next to each value to an array setup
    ' To handle up to 5 values (0-4) - am Ignoring value 0 for convenience

    asnVal(.Fields(0).Value)=asnVal(.Fields(0).Value) + .Fields(1).Value
    .MoveNext
  Loop
End With

For x = 1 to 4
  debug.print asnVal(x)
Next x

Set rs = Nothing
Set dbs = Nothing
End Sub

Mike

 

[Sully38]

This worked very well.. Thank You