Quering multiple related queries and unrelated table

[Nathan87]

Hello,


I have a question with regards to attaching multiple queries to a related table and then also and attaching an unrelated table.


I'm trying to learn Access by make an inventory database that contains in part an items table, an attachment list table, and a database properties table. The database properties table only has one row of data (folder location of attachments is in the table). The attachments table is a list of attachments that link up with the items and there are 3 different types of attachments possible (picture, receipt, document). There can be multiple attachments for each item.


I have 3 queries that contain totals numbers of attachments for each item. They are Total queries.

Query 1: Number of pictures for item

Query 2: Number of receipts for item
Query 3: Number of documents for item


My problem comes in the 4th query that I'm trying to make that will be used as a source for an item detail form. I would like the query to contain the item tables linked with the 3 count queries and then also link to the database properties table. If I attach the 3 count queries to the items table it work just fine, and if I attach just the database properties table to the item table it works just fine, but for some reason I can't do both in one query. I have it working by using 2 queries (1 to attach count queries to the item table, and then another to attach the properties table), but it seems like I should be able to do it in one query.

Being that I'm just starting trying to learn, I'd like to learn the clean ways of building the database and try to avoid having a bunch of band aids for things that I don't understand how to do. I figure I must be missing some concept here with the query.


Thanks in advance for any advice.


Nathan

 

[CJ_London]

if your database properties table has only one row, there is no need to link it to another table, so your sql would look something like this

select * from tblProperties, qry1 inner join qry2 on qry1.fld1-=qry2.fld2

the only time this won't work is if your other tables/queries in your query have left or right joins.

If they have you need to put these into a sub query or separate query.

 

[Nathan87]

I think that is what I was trying, but it wasn't working. I wasn't sure how to take a snap shot of the query to post on here, but I was thinking in terms of the design view. I should have posted the SQL view as that is easy to copy and paste. Sorry about that.

Here is my original query that doesn't include the properties table, but does work:

SELECT ItemTable.ID, ItemTable.Name, ItemTable.Description, ItemTable.Condition, ItemTable.AcquiredDate, ItemTable.PurchasedPrice, ItemTable.CurrentValue, ItemTable.Category, ItemTable.ModelNumber, ItemTable.SerialNumber, ItemTable.PurchasePlace, ItemTable.Insured, ItemTable.Location, ItemTable.Manufacturer, ItemTable.Comments, ItemTable.RetiredDate, ItemTable.DateCreated, ItemTable.DateModified, PictureCntQry.CountOfID, ReceiptCntQry.CountOfID, DocumentationCntQry.CountOfID
FROM ((ItemTable LEFT JOIN PictureCntQry ON ItemTable.ID = PictureCntQry.ItemID) LEFT JOIN ReceiptCntQry ON ItemTable.ID = ReceiptCntQry.ItemID) LEFT JOIN DocumentationCntQry ON ItemTable.ID = DocumentationCntQry.ItemID;

I just tried this, which I think is what you were saying:
New text is colored green.

SELECT ItemTable.ID, ItemTable.Name, ItemTable.Description, ItemTable.Condition, ItemTable.AcquiredDate, ItemTable.PurchasedPrice, ItemTable.CurrentValue, ItemTable.Category, ItemTable.ModelNumber, ItemTable.SerialNumber, ItemTable.PurchasePlace, ItemTable.Insured, ItemTable.Location, ItemTable.Manufacturer, ItemTable.Comments, ItemTable.RetiredDate, ItemTable.DateCreated, ItemTable.DateModified, PictureCntQry.CountOfID, ReceiptCntQry.CountOfID, DocumentationCntQry.CountOfID, DatabasePropertiesTable.AttachmentFolderPath
FROM DatabasePropertiesTable, ((ItemTable LEFT JOIN PictureCntQry ON ItemTable.ID = PictureCntQry.ItemID) LEFT JOIN ReceiptCntQry ON ItemTable.ID = ReceiptCntQry.ItemID) LEFT JOIN DocumentationCntQry ON ItemTable.ID = DocumentationCntQry.ItemID;

When I try and run that I get:
"The SQL statement could not be executed because it contains ambigous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statment."

Is it just the kind of joins I'm using that I have to do them seperately? It seems like it should be straight forward to add the table that isn't related and only has one row.

 

[CJ_London]

As I suggested, it is because you have left joins and the solution is to create a subquery, subtable, or you can use dlookup. Which option is best is dependent on requirements but these are the options.

1. Dlookup

....., DocumentationCntQry.CountOfID, [COLOR="SandyBrown"]Dlookup("AttachmentFolderPath","DatabasePropertiesTable") as AttachmentFolderPath
FROM ((ItemTable[/COLOR] LEFT JOIN PictureCntQry ON ItemTable.ID = PictureCntQry.ItemID) LEFT JOIN ReceiptCn....

2. Subquery

....., DocumentationCntQry.CountOfID[COLOR="SandyBrown"], (SELECT TOP 1 AttachmentFolderPath FROM DatabasePropertiesTable) as AttachmentFolderPath
FROM ((ItemTable LEFT[/COLOR] JOIN PictureCntQry ON ItemTable.ID = PictureCntQry.ItemID) LEFT JOIN ReceiptCn....

3. Subtable

[COLOR="SandyBrown"]SELECT * FROM ([/COLOR]SELECT ItemTable.ID, ItemTable.Name, ItemTable.Description, ItemTable.Condition, ItemTable.AcquiredDate, ItemTable.PurchasedPrice, ItemTable.CurrentValue, ItemTable.Category, ItemTable.ModelNumber, ItemTable.SerialNumber, ItemTable.PurchasePlace, ItemTable.Insured, ItemTable.Location, ItemTable.Manufacturer, ItemTable.Comments, ItemTable.RetiredDate, ItemTable.DateCreated, ItemTable.DateModified, PictureCntQry.CountOfID, ReceiptCntQry.CountOfID, DocumentationCntQry.CountOfID
FROM ((ItemTable LEFT JOIN PictureCntQry ON ItemTable.ID = PictureCntQry.ItemID) LEFT JOIN ReceiptCntQry ON ItemTable.ID = ReceiptCntQry.ItemID) LEFT JOIN DocumentationCntQry ON ItemTable.ID = DocumentationCntQry.ItemID[COLOR="SandyBrown"]) AS queries, DatabasePropertiesTable;[/COLOR]

Of these, since your properties table only has 1 record, eitter option 1 or 2 will do

 

[Nathan87]

Thanks a lot CJ for the answer and for the different options to work around.

For my own curiosity's sake, what is the logic behind not being able to add the unrelated 1 record table to left joins? For my case it seems like that shouldn't cause any problems, but I'm suspecting that there is something that I'm not seeing that could maybe come up in another case. Just trying to understand it in case it applies to something else as well.

I had no idea you could put a sub-query right into another query like that. That seems like a real nice clean way of doing it. I like that a lot.

Thanks again.

 

[CJ_London]

options 1 and 2 are pretty much the samei what they do - you can use them to find other records in the same dataset for example. However both will only return a single value - if you require more values then you need to use option 3.

Option 1, Dlookup, DMax, DSum etc are all functions and as such will be slower than option 2 - probably only noticeable if the table being queried has thousands of records.

Option 3 is usually the fastest way but cannot always be used if you are trying to find a record in relation to the main table which you cannot create a join - for example 'find the previous record'

You can also use options 1 and 2 in the criteria - particularly useful for history tables

With regards the logic behind not being able to have a query with left or right joins and a separate unjoined table, a left joins shows all the records from one table and only those from the other where there is a match. Unjoined tables (called a cartesian query) shows all records from both tables against each record. So two tables each with 10 records would result in a 100 row result. These two processes are incompatible. Think of it in terms of a maths formula, sometimes you need to use brackets to complete a part of the formula before 'moving up' to the next level - then look at the brackets around the query in option 3. At least, that's how I think of it

 

[Nathan87]

Makes sense. Thanks a lot for all the great info CJ.