473,326 Members | 2,680 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Need help with link tables!

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.

9 2057
NeoPa
32,556 Expert Mod 16PB
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
tsizmo
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
32,556 Expert Mod 16PB
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
tsizmo
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
tsizmo
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
32,556 Expert Mod 16PB
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
tsizmo
8
I will,
bytes.com is definitely the place to ask!
Aug 31 '17 #10

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

Similar topics

0
by: Adam Haskell | last post by:
Ok heres the situation: We have a linked server from SQL 2000 to a foxpro dbf. In our test enviroment we had the Foxpro and SQL server on the same machine. The linked worked perfect. Now we are...
2
by: David | last post by:
Hi, We have an internal network of 3 users. Myself & one other currently have individual copies of the front-end MS Access forms and via our individual ODBC links we have used the: File > Get...
11
by: Randy Harris | last post by:
I have been using "IN" to query tables in Oracle from MS Access very successfully. Select Field FROM MyTable IN [ODBC...etc Works great if there is only one table involved. Anyone know how I...
3
by: mvivar | last post by:
Dear guys: First of all, I have to thank all of you, because I have found lots of answers since months without having to call your attention to me until now. I have a database in access 97...
5
by: Luis E Valencia | last post by:
I need a link on a datagrid, the link must have fields of the database Like this acciones.aspx?iddireccion=1&idindicador=4 Thanks
7
by: guest | last post by:
Hi, I need help on this error. I am trying to link tables from a Sybase Database to MS Access, using Sybase system 11. I tried conneting to the database using SQL advantage and it does connect...
17
by: aquablade | last post by:
I have an Oracle 10g R2 database where my ERP transactions reside. I'm using MS Access to grant access to my power users where I use MS Access' Link Tables to provide direct access. I noticed that...
20
by: Dan2kx | last post by:
Hi peeps, i have a current need to change the linked tables in my latest access project, and want to do it using VB. normally i like most people use the linked tables manager. What i am...
35
by: Mihail | last post by:
Hi ! Scenario: 3 front end databases based on the same back end database. All front end parts are in the same folder (=> I know their paths) and have the same structure of linked tables. One...
3
by: pradeepatta | last post by:
We have an ms access application and tables in that are linked with backend database SQL SERVER 2008 I used to manually link the access tables with SQL tables with the help of 'Table Link Manager'...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.