Joining two queries (2000)

Topic

New Reply

I have two separate queries. The fields are:
Invoice Date grouped by Month, Branch, ServParts
Transaction Date grouped by Month, Branch, AllParts

I need to perform a calculation with the two types of parts, so I’m creating a third query.

I tried to join the two queries by dates and branch because I want Month, Branch, ServParts, All Parts, then I’ll add my calculation
After adding the second query and the fields in the design view, I try to preview the data and I get an error message:
“The field is too small to accept the amount amount of data you attempted to add. Try inserting or pasting less data.”

I haven’t had this problem with my other query combinations (which only needed to be joined by branch) so I removed the date join.
After the months aren’t joined anymore, the query displays the data (but of course, I have an undesirable Cartesian product)
This leads me to believe it doesn’t actually have anything to do with column size.

I’m not quite sure what to try… has anyone seen this before??

Thanks.
Chris

Reply | Quote

Replies

I am puzzled by this question because I would expect that a query whose fields are:
invoice Date grouped by Month, Branch, ServParts
would probably not contain any dates.
When you run a grouping query, the field that is not grouped has an aggregreate function (usually sum or count) and ends up returning a number. (It could be max, min. first etc in which case it would be a date).
What aggregate function do you have on the dates?

Reply | Quote

Each query is built from a more detailed query that has work order #, branch, invoice date, quantity, cost…
I use the detailed query as a source and choose date, branch and cost as my columns
When I choose sum for the cost, I get a choice about grouping the dates – unique date/time, week, month, year…
After I choose month, the query is displayed with the month, the branch and the cost at that branch for that month.
I think it might see the month columns as text, but it is using the dates to figure out what to sum.
There was no need put any functions on the actual dates.

Let me know if you need more information. I still haven’t figured this out!
Thanks
Chris

Reply | Quote

to jump in here, but why don’t you post the queries here and we can have a look at them.
Sometimes it is a lot easier to solve problems like this if we can actually see what you are trying to do.
Pat

Reply | Quote

Hi patt – good idea.
It took me awhile to get below 100 K though!!
When you try to preview, the date range still in the data is June 29 2001 – July 2 2001

I want to give them a trend report with a graph that shows them the value of the measurement every month between the range. I left a few of the working reports to show you what I mean.
My problem is joining the DCAL OTC Part Trend query to the DCAL Service Part Trend query.
I need to do that so I can add a calculation using fields from both tables
(See the DCAL Summary query for the working version of the equation – no date columns…)

The results of this effort are DCAL Trend and DCAL Trend 2
(In one I started with service and added the OTC, in the other I started with the OTC and added service – it made no difference)

When I try to preview the data in either DCAL Trend query, I get an error message about field size
If I remove the join relationship between JHINDT by month and HDATE by month, the query will display data – but I have a Cartesian product.

Thanks in advance for any help!!
Chris

Reply | Quote

Go into queries “DCAL Service Part Trend” and “DCAL OTC Part Trend” and set the right hand field’s show condition.
Then go into query “DCAL Trend” and delete the relationship between “JHINDT by Month” and “HDATE by Month”, then introduce a relationship between the new fields (EXPR1003 I think, it may be different on yours).
This should then give you the correct results, I hope.
Pat

Reply | Quote

I have another suggestion.
In both the queries “DCAL Service Part Trend” and “DCAL OTC Part Trend” it seemed to me that the right hand field, the third group by, is just repeating the grouping of the first field in a different format. Both fields effectively group by month. When I deleted this last field from both queries, the join worked OK.
Each query gave the same results as previously as well.

Reply | Quote

Thank you both for the help!
I had that GROUP BY in there so the months would sort chronologically (instead of alphabetically by first letter of the month), but it is losing the sort in the reports anyway….
I’d much rather have the calculation work!!

Thank you again.
Chris

Reply | Quote