1
votes

I want to create a query in MS Access that will display information from two tables based on the values in one table. Both of these tables have the same exact columns. One has set records and the other one has records a visitor can insert/edit/delete. For the purpose of this question I will call the tables TableA and TableB. TableA has the predetermined records and can not be changed. Multiple users will be using these records. Visitors would add records to TableB. I need a query that will display the records from TableA unless a visitor adds a record to TableB and then it displays that record. The field I need to join on is CategoryID. So what I need is basically like this;

If TableB.CategoryID Is Not Null Then 
Select * From TableB
Else
Select * From TableA
End If

Thanks for any assistance anyone can provide.

JW

3

3 Answers

0
votes

You get part of the way there by unioning the individual table queries; that works if there's nothing in B, but shows the A records if there is.

So suppose we created a table just like A, say A2, but with an added column: the number of records in B. And then we select all of the records in A2 where this new column 0, and only the columns originally in A; call this A3.

Now consider the union of A3 & B. If B is empty, we get A. If B is not empty, then none of the records from A2 will be chosen for A3, and we'e left with just B.

0
votes

That is easier than it seems at first. You'll have to join both tables on CategoryID and then conditionally select the right item like this:

SELECT tA.CategoryID, IIF(tB.CategoryID IS NULL, tA.txtEntry, tB.txtEntry) AS EntryText, 
tB.CategoryID IS NULL AS bOriginalEntry
FROM TableA AS tA LEFT JOIN TableB AS tB ON tA.CategoryID=tB.CategoryID

However there is one caveat: If TableB is empty then the join is producing an empty set! Just populate TableB with at least one record (preferably one with an invalid CategoryID so it won't join with a valid record in TableA. The bOriginalEntry is just a boolean expression to show whether the EntryText stems from TableA or TableB.

0
votes

I found this thread searching for a similar problem. Note to self and others. You can use the Join Types to cope with potential different values in a conditional select, MS Access doesn't have the full range of JOIN that MS SQL has, but you can "fudge" it. eg Full outer joins: all the data, combined where feasible In some systems, an outer join can include all rows from both tables, with rows combined when they correspond. This is called a full outer join, and Access doesn’t explicitly support them. However, you can use a cross join and criteria to achieve the same effect. https://support.office.com/en-us/article/join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6#typesofjoins