By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Need help with link tables!

P: 8
Hi,
I need help with a query based on 2 linked tables(one to many).

TABLE 1 has 4 fields INDEX(primary key), BRAND, CATEGORY, TYPE
TABLE 2 has 4 fields ID(prim. key), INDEX(foreign key),LOCATION, DATE.

I want the query to return all records of TABLE 1 and only the newest DATE and LOCATION of TABLE 2.

In query desidn i use TOTAL:"group by" in all "TABLE 1" fields
and in "TABLE 2" TOTAL": "MAX" in DATE Field.

The problem is i can't get the right(match) LOCATION of the DATE field.
Neither "MAX" or "LAST" on LOCATION TOTAL returns the match location of that specific date.

One thing that makes this more difficult i think is the fact that i want to be able to add "freely" my records in table 2.
Like, add a new record with oldest date than the current date.
Then i can always get the newest date, but LOCATION LAST gives me the last LOCATION added that is not the newest.

Thanks!
Aug 29 '17 #1

✓ answered by NeoPa

The first query seems to be exactly the sort of thing I was suggesting. SQL will have just as much trouble dealing with a maximum value when there are multiple items with that same value as you or I would. Yes. You'll quite sensibly (though possibly not so desirably) have multiple records returned in that scenario.

To get around this it seems you've aggregated the second query and selected Last() of the [ΤΟΠΟΘΕΤΗΣΗ] field. That's a very sensible approach and works - assuming as I do that what you want is only one, but any one, of the records that match Max([ΗΜ/ΝΙΑ]).

Having chosen that course though it may make better sense to handle that in your first query thus :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [BARCODE]
  2.        , [ΗΜ/ΝΙΑ]
  3.        , First([t1].[ΤΟΠΟΘΕΤΗΣΗ]) AS [ΤΟΠΟΘΕΤΗΣΗ]
  4. FROM     [HISTORY] AS [t1]
  5.          INNER JOIN
  6.          (SELECT   [BARCODE]
  7.                  , Max([ΗΜ/ΝΙΑ]) AS [MaxDT]
  8.           FROM     [HISTORY]
  9.           GROUP BY [BARCODE]) AS [t2]
  10.   ON     ([t1].[BARCODE]=[t2].[BARCODE])
  11.  AND     ([t1].[ΗΜ/ΝΙΑ]=[t2].[MaxDT])
  12. GROUP BY [BARCODE]
  13.        , [ΗΜ/ΝΙΑ]
I've removed some fields from the SELECT clause as they don't seem necessary but you can add them back if they're used elsewhere.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,418
Indeed. That's exactly what you'd expect in such a case. What you're after is like saying (Pseudo-SQL - NOT valid) :
Expand|Select|Wrap|Line Numbers
  1. FROM   [TABLE 1]
  2.        x JOIN
  3.        [TABLE 2]
  4.   ON   [TABLE 1].[INDEX]=[TABLE 2].[INDEX]
  5. WHERE  ([TABLE 2].[DATE]=Max([TABLE 2].[DATE]))
The problem with that is that SQL won't recognise Max([TABLE 2].[DATE]) in the WHERE clause. Once you use a GROUP BY clause you've lost any chance of identifying the [TABLE 2] record correctly at all.

You need to use a Subquery (Subqueries in SQL). This can be done by designing a separate QueryDef and using that but it needn't. See the linked article for handling the same thing within an existing set of SQL.

In case you aren't familiar with the concept, all Access QueryDef objects have stored within them the actual SQL used for running them. This is what I'm referring to above. For most QueryDefs you can switch between views using the View menu (Pre-2007) or the options on the right of the Status Bar (Post 2003). While Subqueries are displayed in Design view they cannot be amended in that view. Only in SQL view.
Aug 30 '17 #2

P: 8
Thank's for replying!
What i've done so far is use 2 queries:
1:"SearchqDT2" for "matching" date and location on table 2 "HISTORY"(It works but i get duplicates if i have multiple records with the same date..)

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT t1.Αναγνωριστικό, t1.BARCODE, t1.[ΗΜ/ΝΙΑ], t1.ΤΟΠΟΘΕΤΗΣΗ, t1.ΙΣΤΟΡΙΚΟ
  2. FROM HISTORY AS t1 INNER JOIN (SELECT BARCODE, Max([ΗΜ/ΝΙΑ]) AS MaxDT FROM HISTORY GROUP BY BARCODE)  AS t2 ON (t1.[ΗΜ/ΝΙΑ] = t2.MaxDT) AND (t1.BARCODE = t2.BARCODE);
And another "Searchq" with Table 1 "EGGRAFES" and instead of table 2 i used the query "SearchqDT2"

Expand|Select|Wrap|Line Numbers
  1. SELECT EGGRAFES.BARCODE, EGGRAFES.ΕΙΔΟΣ, EGGRAFES.ΜΑΡΚΑ, EGGRAFES.ΜΟΝΤΕΛΟ, EGGRAFES.[Serial Number], Last(SearchqDT2.ΤΟΠΟΘΕΤΗΣΗ) AS ΤελευταίοΤουΤΟΠΟΘΕΤΗΣΗ, EGGRAFES.LABELED, EGGRAFES.ΣΗΜΕΙΩΣΕΙΣ, Last(SearchqDT2.[ΗΜ/ΝΙΑ]) AS [ΤελευταίοΤουΗΜ/ΝΙΑ]
  2. FROM EGGRAFES INNER JOIN SearchqDT2 ON EGGRAFES.BARCODE=SearchqDT2.BARCODE
  3. GROUP BY EGGRAFES.BARCODE, EGGRAFES.ΕΙΔΟΣ, EGGRAFES.ΜΑΡΚΑ, EGGRAFES.ΜΟΝΤΕΛΟ, EGGRAFES.[Serial Number], EGGRAFES.LABELED, EGGRAFES.ΣΗΜΕΙΩΣΕΙΣ
  4. HAVING (((EGGRAFES.ΕΙΔΟΣ) Like "*" & Forms!SearchForm!EIDOS & "*") And ((EGGRAFES.ΜΑΡΚΑ) Like "*" & Forms!SearchForm!MARKA & "*") And ((EGGRAFES.ΜΟΝΤΕΛΟ) Like "*" & Forms!SearchForm!MONTELO & "*") And ((Last(SearchqDT2.ΤΟΠΟΘΕΤΗΣΗ)) Like "*" & Forms!SearchForm!TOPO & "*") And ((EGGRAFES.LABELED) Like "*" & Forms!SearchForm!hidlblchoice & "*") And ((Last(SearchqDT2.[ΗΜ/ΝΙΑ]))>=Forms!SearchForm!dtfrom And (Last(SearchqDT2.[ΗΜ/ΝΙΑ]))<=Forms!SearchForm!dtto))
  5. ORDER BY EGGRAFES.BARCODE;
I run some tests and it seems to work, what do you think?
Aug 30 '17 #3

NeoPa
Expert Mod 15k+
P: 31,418
The first query seems to be exactly the sort of thing I was suggesting. SQL will have just as much trouble dealing with a maximum value when there are multiple items with that same value as you or I would. Yes. You'll quite sensibly (though possibly not so desirably) have multiple records returned in that scenario.

To get around this it seems you've aggregated the second query and selected Last() of the [ΤΟΠΟΘΕΤΗΣΗ] field. That's a very sensible approach and works - assuming as I do that what you want is only one, but any one, of the records that match Max([ΗΜ/ΝΙΑ]).

Having chosen that course though it may make better sense to handle that in your first query thus :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [BARCODE]
  2.        , [ΗΜ/ΝΙΑ]
  3.        , First([t1].[ΤΟΠΟΘΕΤΗΣΗ]) AS [ΤΟΠΟΘΕΤΗΣΗ]
  4. FROM     [HISTORY] AS [t1]
  5.          INNER JOIN
  6.          (SELECT   [BARCODE]
  7.                  , Max([ΗΜ/ΝΙΑ]) AS [MaxDT]
  8.           FROM     [HISTORY]
  9.           GROUP BY [BARCODE]) AS [t2]
  10.   ON     ([t1].[BARCODE]=[t2].[BARCODE])
  11.  AND     ([t1].[ΗΜ/ΝΙΑ]=[t2].[MaxDT])
  12. GROUP BY [BARCODE]
  13.        , [ΗΜ/ΝΙΑ]
I've removed some fields from the SELECT clause as they don't seem necessary but you can add them back if they're used elsewhere.
Aug 30 '17 #4

P: 8
Thank you very much for your help and advice!
Your last suggestion was what i wanted at first,
adding two queries in one is always better :)

Thanks again!
Jim
Aug 30 '17 #5

NeoPa
Expert Mod 15k+
P: 31,418
A pleasure Jim.

It was an interesting question that highlighted some important points. Your first stab was pretty impressive in its own right if I'm honest.

Good job!

PS. During the earlier post with your field names I had to work on it in a separate text editor that didn't handle any of the foreign words/letters so I had to copy & paste them from your post over the top of mine where they had ended up as '?'s.
Aug 30 '17 #6

NeoPa
Expert Mod 15k+
P: 31,418
By the way, it may help to understand why I used First() instead of Last() in my suggested version.

Which value is selected isn't important, but unlike many of the aggregation functions, First() doesn't need to process the complete recordset to arrive at a value. Last() does, of course. Once the first record is found and processed the processing can be aborted. Last() can do the same if it's based on an index (Sargable) by traversing the index in reverse order, but without an index it must complete the recordset before knowing which is last.

Such things are matters which often make negligible difference in a particular situation. Nevertheless, the understanding of them is always important IMHO.
Aug 30 '17 #7

P: 8
It was a difficult sql task for me NeoPa so i had to improvise
and make things work. Therefore i focus on the results i wanted
to get, and "simulate" the task with two queries.
Also sorry for the foreign text, it is wise ofcourse next time to avoid this ;)
As for the First() and Last() functions thank you for your advice
and explanation. It is very helpfull to understand the how these
functions are handled by the database mechanism and decite which one to use next time.

Cheers
Jim
Aug 31 '17 #8

NeoPa
Expert Mod 15k+
P: 31,418
It was a pleasure to work on Jim. Don't worry too much about the foreign characters. It was only due to taking them to another editor that I had the issue. Also, it wasn't a problem. Just a point of interest/amusement really.

I hope you'll come back with more questions if ever you have need.
Aug 31 '17 #9

P: 8
I will,
bytes.com is definitely the place to ask!
Aug 31 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.