Unsure how to go about creating this query

[akn112]

I have been trying to create a select query which works with the following tables:

tblRecords tblRef

Index type(eri,ltr,yrr)
date date
qty


What happens is i need it to:
-Look in tblRecords for the month and year
-match the month and year in tblRef (should be unique in tblRef)
-find out what type(eri,ltr,yrr) the month/year combo is
-sum the qty from all the entries in tblRecords which corresponds to that type and display as a field in my query

I'm not sure how to even start this query. I've tried to think of build in VBA functions but can't seem to figure out a good way. Anybody have ideas? Thanks!

btw: attached is a test db

[orange]

I have been trying to create a select query which works with the following tables:

tblRecords tblRef

Index type(eri,ltr,yrr)
date date
qty

What happens is i need it to:
-Look in tblRecords for the month and year
-match the month and year in tblRef (should be unique in tblRef)
-find out what type(eri,ltr,yrr) the month/year combo is
-sum the qty from all the entries in tblRecords which corresponds to that type and display as a field in my query

I'm not sure how to even start this query. I've tried to think of build in VBA functions but can't seem to figure out a good way. Anybody have ideas? Thanks!

btw: attached is a test db

I looked at your sample database.
tblRecords has data for 1999/2000
tblRef has data for 2003 - 2007

No records will match based on the data values????????

[akn112]

lolz, bad example, i only took a subset from tblRecords. In reality, my tblRecords has 800,000+ records. I'll upload a better one.

[orange]

lolz, bad example, i only took a subset from tblRecords. In reality, my tblRecords has 800,000+ records. I'll upload a better one.
Try this query:


SELECT
Format([tblRef]![Month],"yyyymm") AS ReviewPeriod_YYYYMM, tblRef.Type
, Sum(tblRecords.qty) AS SumOfQty
FROM tblRecords, tblRef
WHERE (((Format([tblRef]![Month],"yyyymm"))=Format([tblRecords]![Month],"yyyymm")))
GROUP BY Format([tblRef]![Month],"yyyymm"), tblRef.Type;


You have to get the Year and Month from your field [Month] in each Table; set up a query to show where these Year & Month fields are equal in the 2 tables; Group by Year & Month and also by Type and Sum the qty for each group.

Hope this helps.

[akn112]

thanks orange. I tried it out but i'm getting a memory error. I tried increasing the max locks and setting it to "no transaction" but still no go. There other way to get around this? Here's a snippet of my maxlocks function


Sub LargeUpdate(strQueryName As String)
On Error GoTo LargeUpdate_Error
Dim db As DAO.Database, ws As DAO.Workspace
' Set MaxLocksPerFile.
DBEngine.SetOption dbMaxLocksPerFile, 400000
Set ws = Workspaces(0)
' Perform the update.
ws.BeginTrans
DoCmd.OpenQuery strQueryName
ws.CommitTrans
Exit Sub
LargeUpdate_Error:
MsgBox Err & " " & Error
ws.Rollback
End Sub

[orange]

thanks orange. I tried it out but i'm getting a memory error. I tried increasing the max locks and setting it to "no transaction" but still no go. There other way to get around this? Here's a snippet of my maxlocks function


Sub LargeUpdate(strQueryName As String)
On Error GoTo LargeUpdate_Error
Dim db As DAO.Database, ws As DAO.Workspace
' Set MaxLocksPerFile.
DBEngine.SetOption dbMaxLocksPerFile, 400000
Set ws = Workspaces(0)
' Perform the update.
ws.BeginTrans
DoCmd.OpenQuery strQueryName
ws.CommitTrans
Exit Sub
LargeUpdate_Error:
MsgBox Err & " " & Error
ws.Rollback
End Sub
Did you try the query as a separate test? Did you get the query to work or get the query that you need to work (before trying the whole enchalada??)?

[akn112]

yeup, i tried it seperately first, then tried it with my largeupdate sub. Reason it's probably running into this problem is because the full recordset has about 18 fields and to group 15/18 fields is probably taking up alot of Access' memory (records probably added exponentially in temp table while running)

[orange]

When you tried the query by itself, did it work?

In your sample data you have a field [month] that is really a date. You have records by day. You could do some interim calculations to temp table(s) that sum you values by month, then use other means to get your final outputs.

If you are retrieving data where doe the Update come from? The query I was responding to was a Select (Read only) query.
If it's retieval why the recordlocks?

How many simultaneous users on the database?
Do you have a test database where you can test this?