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!
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 : - SELECT [BARCODE]
-
, [ΗΜ/ΝΙΑ]
-
, First([t1].[ΤΟΠΟΘΕΤΗΣΗ]) AS [ΤΟΠΟΘΕΤΗΣΗ]
-
FROM [HISTORY] AS [t1]
-
INNER JOIN
-
(SELECT [BARCODE]
-
, Max([ΗΜ/ΝΙΑ]) AS [MaxDT]
-
FROM [HISTORY]
-
GROUP BY [BARCODE]) AS [t2]
-
ON ([t1].[BARCODE]=[t2].[BARCODE])
-
AND ([t1].[ΗΜ/ΝΙΑ]=[t2].[MaxDT])
-
GROUP BY [BARCODE]
-
, [ΗΜ/ΝΙΑ]
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) : - FROM [TABLE 1]
-
x JOIN
-
[TABLE 2]
-
ON [TABLE 1].[INDEX]=[TABLE 2].[INDEX]
-
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 QueryDef s 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.
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..) - SELECT DISTINCT t1.Αναγνωριστικό, t1.BARCODE, t1.[ΗΜ/ΝΙΑ], t1.ΤΟΠΟΘΕΤΗΣΗ, t1.ΙΣΤΟΡΙΚΟ
-
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" - SELECT EGGRAFES.BARCODE, EGGRAFES.ΕΙΔΟΣ, EGGRAFES.ΜΑΡΚΑ, EGGRAFES.ΜΟΝΤΕΛΟ, EGGRAFES.[Serial Number], Last(SearchqDT2.ΤΟΠΟΘΕΤΗΣΗ) AS ΤελευταίοΤουΤΟΠΟΘΕΤΗΣΗ, EGGRAFES.LABELED, EGGRAFES.ΣΗΜΕΙΩΣΕΙΣ, Last(SearchqDT2.[ΗΜ/ΝΙΑ]) AS [ΤελευταίοΤουΗΜ/ΝΙΑ]
-
FROM EGGRAFES INNER JOIN SearchqDT2 ON EGGRAFES.BARCODE=SearchqDT2.BARCODE
-
GROUP BY EGGRAFES.BARCODE, EGGRAFES.ΕΙΔΟΣ, EGGRAFES.ΜΑΡΚΑ, EGGRAFES.ΜΟΝΤΕΛΟ, EGGRAFES.[Serial Number], EGGRAFES.LABELED, EGGRAFES.ΣΗΜΕΙΩΣΕΙΣ
-
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))
-
ORDER BY EGGRAFES.BARCODE;
I run some tests and it seems to work, what do you think?
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 : - SELECT [BARCODE]
-
, [ΗΜ/ΝΙΑ]
-
, First([t1].[ΤΟΠΟΘΕΤΗΣΗ]) AS [ΤΟΠΟΘΕΤΗΣΗ]
-
FROM [HISTORY] AS [t1]
-
INNER JOIN
-
(SELECT [BARCODE]
-
, Max([ΗΜ/ΝΙΑ]) AS [MaxDT]
-
FROM [HISTORY]
-
GROUP BY [BARCODE]) AS [t2]
-
ON ([t1].[BARCODE]=[t2].[BARCODE])
-
AND ([t1].[ΗΜ/ΝΙΑ]=[t2].[MaxDT])
-
GROUP BY [BARCODE]
-
, [ΗΜ/ΝΙΑ]
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.
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
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.
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.
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
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.
I will,
bytes.com is definitely the place to ask!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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'...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
| |