Good Morning!
I have an MS Access split database (not using ms sql server (yet)) thats joining a couple of tables with a temp query (imaginatively called "temp"!)
SELECT table_CORE.[core-ID],
table_CORE.[PO],
table_CORE.[Part],
table_CORE.[Part Rev],
table_CORE.[Supplier Doc #],
table_CORE.[Sup Doc Rev],
table_DATES.[date-ID],
table_DATES.[iteration],
table_REJECTIONS.[rejection-ID],
table_REJECTIONS.[iteration],
table_REJECTIONS.[reason],
table_REJECTIONS.[details],
table_REJECTIONS.[timestamp]
FROM
(
(
table_CORE INNER JOIN [TEMP]
ON table_CORE.[core-ID] = TEMP.[date-ID]
)
INNER JOIN [table_DATES]
ON TEMP.[date-ID] = table_DATES.[date-ID]
AND TEMP.[iteration]=table_DATES.[iteration]
)
INNER JOIN table_REJECTIONS
ON TEMP.[date-ID] = table_REJECTIONS.[rejection-ID]
AND TEMP.[iteration]=table_REJECTIONS.[iteration]
WHERE table_CORE.[core-ID] Like '*'
So ultimately, this displays a list of document rejections - which is fine, except that each document applies to multiple parts.
You can tie up PO, Part and Part Rev to collapse these rejections into a single document - the problem is I would usually do this with a GROUP BY claus...
...except the "details" field is a memo datatype, and access truncates it to 256 characters when you try the GROUP BY clause.
Any ideas how to work around this? I've tried the group clause before joining it to the rejeciton table, but I end up with the same problem (in that there are multiple rejections with the same PO, Part Number and Revision tied together) - and I'm investigating other methods, but I just can't get my head round the SQL required for it!
Many thanks!
If you need any more details, just give me a shout!
---Additional details---
Example:
ID PO Part Rev reason details
1 111 A1 A error some_details1
2 111 A1 A error some_details1
3 111 A1 A error some_details1
4 222 A1 A error some_details1
6 222 A1 A error some_details1
7 333 B1 A error some_details1
8 333 B1 A error some_details1
9 444 B1 A error some_details1
10 555 C1 A error some_details1
needs to be changed to:
PO Part Rev reason details
111 A1 A error some_details1
222 A1 A error some_details1
333 B1 A error some_details1
444 B1 A error some_details1
555 C1 A error some_details1
Note on how the join function is working:
current items are stored in CORE, and tied up to dates with a one-to-many relationship by core-ID>>date-ID, which then uses iteration to get he most recent event in the part's history.
That most recent event is then joined up to the rejection list using core-ID>>rejeciton-ID AND date-iteration>>rejectioniteration to return the currently active rejections.
It turns out I could cheat and get MS to write my SQL for me!
Following the advice from AllenBrowne in the query editor (instead of working with the SQL Directly), and aggregating table_REJECTIONS.details using FIRST() instead of GROUP BY() has fixed it!
What a nuisance that was to code for though - the raw SQL for the query is shown below.
SELECT table_CORE.PO,
table_CORE.Part,
table_CORE.[Part Rev],
table_CORE.[Supplier Doc #],
table_CORE.[Sup Doc Rev],
table_REJECTIONS.reason,
First(table_REJECTIONS.details) AS details
FROM
(
(
table_CORE INNER JOIN TEMP
ON table_CORE.[core-ID] = TEMP.[date-ID]
)
INNER JOIN table_DATES
ON
(
TEMP.[iteration] = table_DATES.[iteration]
)
AND
(
TEMP.[date-ID] = table_DATES.[date-ID]
)
)
INNER JOIN table_REJECTIONS
ON
(
TEMP.[iteration] = table_REJECTIONS.[iteration]
)
AND
(
TEMP.[date-ID] = table_REJECTIONS.[rejection-ID]
)
WHERE table_CORE.[core-ID] Like '*'
GROUP BY table_CORE.PO,
table_CORE.Part,
table_CORE.[Part Rev],
table_CORE.[Supplier Doc #],
table_CORE.[Sup Doc Rev],
table_REJECTIONS.reason;