Need Help Dianosing a Query is to complex error message.

[RayMilhon]

I have a query that generates membership by month for the last 12 months. It's a Union query that gets the data of who were members 12 months ago union who were members 11 months ago ....



The query runs perfectly and I get the data I need but it's a bit slow. So I created a make table query based on that query to put the fields into a table. The table only has to be refreshed once a month and I have a number of reports based on that table. I had to make a minor change to the union query and it works as expected but now when I try to run the make table query I get the error message that the query is too complex. The make table query has only the union query and does nothing but makes a table. There are no joins or anything else in the make table query. How can that be too complex???

[ranman256]

Why not use 1 query for members between 1 and 12 months?

[June7]

Provide query statement for analysis.

[Micron]

I think the question that needs to be asked is, why do you want to see a final query (or a query based on a table you make from it); i.e. what will you do with the end result? Make a report? You could, as ranman says, just query over the last 12 months. I'm thinking you might just want to let a report group on month. Or, you can run the same query 12x with different months. I'll bet you have 12 table fields for months. If your data was vertical instead of horizontal, you wouldn't need union. If I'm totally off base with that guess, then I'm really missing the point of a union query for this.

[RayMilhon]

Provide query statement for analysis.

these are 2 functions called by the query

Function StartDate(Optional intvar As Long) As Date
Dim tmpdate As Date
Dim dbgdate As Date
'tmpdate = Now()
If IsNull(intvar) Or intvar = 0 Then
    StartDate = Format(DateAdd("yyyy", -1, Now()), "mm/dd/yyyy")
    'dbgdate = StartDate
Else
    StartDate = Format(DateAdd("m", -1 * intvar, CDate(Month(Now()) & "/01/" & Year(Now()))), "mm/dd/yyyy")
'    dbgdate = StartDate
End If
'    Debug.Print "Start Date: " & dbgdate
End Function

Public Function Enddate(Optional intvar As Long) As Date
'Dim tmpdate As Date
'Debug.Print intvar
'Enddate = DateAdd("d", -1, Now())
If intvar = 0 Then
    Enddate = DateAdd("D", -1, Now())
Else
    Enddate = DateAdd("d", -1, DateAdd("d", -1, StartDate(intvar)))
End If
''Debug.Print "End Date:" & tmpdate
'Enddate = Format(tmpdate, "mm/dd/yyyy")
End Function

Here's the union query which works fine

SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT, StartDate(12) as mnth,dbo_rvs_memb_company.rev_fullname as mbr
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(12)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(12)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(11),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(11)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(11)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(10),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(10)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(10)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(9),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(9)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(9)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(8),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(8)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(8)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(7),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(7)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(7)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT, StartDate(6),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(6)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(6)));
union all
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(5),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(5)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(5)));
UNION ALL 
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(4),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(4)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(4)));

UNION ALL 
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(3),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(3)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(3)));
UNION ALL 
SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(2),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(2)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(2)));
UNION ALL SELECT dbo_tbl_HPCODEs.PRODUCTLINE, dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID, dbo_RVS_MEMB_COMPANY.PROV_KEYID, dbo_RVS_MEMB_PCPHISTS.PCPFROMDT, dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT,StartDate(1),dbo_rvs_memb_company.rev_fullname
FROM (dbo_RVS_MEMB_PCPHISTS INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_MEMB_PCPHISTS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID) INNER JOIN dbo_tbl_HPCODEs ON dbo_RVS_MEMB_COMPANY.HPCODE = dbo_tbl_HPCODEs.HPCODE
WHERE (((dbo_RVS_MEMB_PCPHISTS.PCPFROMDT)<=enddate(1)) AND ((dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT) Is Null Or (dbo_RVS_MEMB_PCPHISTS.PCPTHRUDT)>StartDate(1)));

and finally the query where the error occurs

SELECT Qry_member_months.PRODUCTLINE, Qry_member_months.MEMB_KEYID, Qry_member_months.PROV_KEYID, Qry_member_months.PCPFROMDT, Qry_member_months.PCPTHRUDT, Qry_member_months.mnth, Qry_member_months.mbr INTO tbl_member_months
FROM Qry_member_months;

The data is on a corporate SQL Server DB that MS Access Links To so I can't provide the data

[RayMilhon]

Micron This is the first step in generating several reports. There are 3 Make table queries run by a macro those 3 tables are queried for the reports that are generated. The Data for the 3 reports is the same it's the presentation of the data that differs for the 3 reports. Doing it in this manner takes me about 45 Minutes to generate all 3 Reports. Basing the Reports off of the queries that make the tables was taking 6 and a half hours.

[RayMilhon]

It's per month so I have to get the members that were active in Jan and the members that were active in Feb even if they're the same member. So say we have 100 Members in Jan. In Feb we lose 3 and add 5 So Feb should be 102 members. so If we average 100 Active members for the year we'll have 1200 Member Months.

[RayMilhon]

Ok, Found it. The Enddate Function in the If Statement was missing the IsNull(intvar) Added that and they query works fine.