Using SQL to populate Row Source from Multiple Tables

[Markb384]

Hi guys.

I have a selection of tables, each containing an equipment ID, a date and then some other fields specific to that table. Each table represents a different type of test that was performed.

For a combobox I have on another form, I have some SQL code to populate the box with unique ID numbers using the format below, which works perfectly (particularly as I know that all the unique IDs are definitely unique due to other code elsewhere).

SELECT tableName.fieldName
FROM tableName
ORDER BY tableName.fieldName

I know have another combobox, in which I want to do something similar. I want to pull out all of the dates from a variety of tables where another field equals a value (in this case, the ID of the equipment), hide duplicates such that each date only appears once and order in chronological order. I know there is SQL code for only retrieving dates which match the ID (using WHERE fieldName = textbox.value for instance) and I have seen something somewhere about hiding duplicates (although I can't remember it at this exact moment). The bit I am struggling with is the SELECT... FROM aspect of the code, as I want to search several tables.

Anyone have any ideas on how to do this efficiently, without having to resort to VBA code to populate and sort arrays etc. (I've been down that route before for Excel and it was a nightmare...)



Thanks in advance.

[ItsMe]

Maybe incorporate GROUP BY? You could start by using the query builder and use the Totals option.

[Markb384]

Thank you ItsMe

Unfortunately, I can't get my head around using the Group By function, as my reading around seems to imply it needs some sort of sum or count, and I don't want that.

Just to clarify, my data is structured like the following...

ID Date Some other fields

and I have several tables of a similar structure, each with different "other fields" depending on the test.

I want to populate a combobox with all of the dates from each table where the ID matches a user input, but without displaying each date in the list more than once. For instance, in one table, equipment ID A1 might have results on dates 1/1/11 and 2/1/11 and in another table A1 might have results for 2/1/11 and 3/1/11. I want my combobox to contain the dates 1/1/11, 2/1/11 and 3/1/11 only when the user selects A1 as the equipment ID.

My current SQL code is

SELECT DISTINCT TableA.[SDate], TableB.[SDate], TableC.[SDate]FROM TableA, TableB, TableC;

which doesn't work (obviously). I am unsure of how to use a WHERE when I am checking the criteria in several tables and ORDER BY as I am not clear on which part to ORDER BY

[Markb384]

Sorry to double post, but I have solved 99% of the problem using a UNION command, e.g.

SELECT DISTINCT TableA.[SDate]
FROM TableA
WHERE TableA.[Equip ID] = [Forms]![Form_Name]![Textbox_Name]
UNION
SELECT DISTINCT TableB.[SDate]
FROM TableB
WHERE TableB.[Equip ID] = [Forms]![Form_Name]![Textbox_Name]
;

Now to try and get it in descending order rather than ascending...

[ItsMe]

In the query builder there is an option to add an ORDER BY statement. Select the ascending or descending option for the appropriate column then view the results in SQL view.

[pbaldy]

A UNION query can't be shown in the query builder, so just add this at the end:

ORDER BY SDate DESC

[ItsMe]

Missed that, thanks Paul.

[pbaldy]

No problem. You're only allowed one slip per month though...