Create query from 2 tables (1 Viewer)

Mr_P · Jan 23, 2014

I need to get the MAX id from two tables for use in vb.net. For example:

Code:

Table1                 Table2
id, customer           id, customer

How do I write a query that will return the last or MAX id from both tables based on a customer criteria. As a new user to access and vb.net any guidance would be an immense help Many thanks

Galaxiom · Jan 23, 2014

Open a recordset against an Aggregate query with a Union subquery.

SELECT Max(id) AS MaxID
FROM
(SELECT id FROM Table1
UNION ALL
SELECT id FROM Table2)
AS A

Mr_P · Jan 23, 2014

@Galaxiom

Thank you for reply. However, it is only returning the value from the first table. Where have i gone wrong. Thanks

Code:

SELECT Max([Request no]) AS MaxID
FROM
(SELECT [Request no] FROM Requests
UNION ALL
SELECT [Request no] FROM [Request Boxes])
AS A
WHERE Customer = "DEMO";

Galaxiom · Jan 23, 2014

I misunderstood your requirement.
Try this one:

Code:

SELECT RequestID, RequestBoxID
FROM
   (
    SELECT Max([Request no]) AS RequestID
    FROM Requests 
    WHERE Customer = "DEMO"
    ) AS A
,
    (
     SELECT Max[Request no] As RequestBoxID
     FROM [Request Boxes])
     WHERE Customer = "DEMO"
     ) AS B
;

Mr_P · Jan 24, 2014

Hi that creates an error of which I have attached screenshot. Thanks

Galaxiom · Jan 27, 2014

Sorry. An extra closing bracket crept in somehow in this line:
FROM [Request Boxes])

Create query from 2 tables (1 Viewer)

Mr_P

Registered User.

Galaxiom

Super Moderator

Mr_P

Registered User.

Galaxiom

Super Moderator

Mr_P

Registered User.

Attachments

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread