Need help - newbie with outer/inner joins issue

[calcal]

Hi,

I am new to using SQL/Access. I am trying to build a query that produces a "bill of materials" for a store remodel. The content of the bill of materials should change based on the type of the store or the type of item (either I need to add items or add additional detail or spec to an item).

For instance, If one of the items listed is an open refrigerator and the refrigerator goes into a store that is not open 24 hours, I should pull the additional spec from the specs table (the additional spec says something like "night curtains required".

So the following query is giving me a syntax error (as soon as I remove the 24hr condition the error goes away but the query brings the "night curtains required" for all the stores, which is not the point).

SELECT Quantities.Store_no, Quantities.Section, Quantities.ItemID, Quantities.Quantity, specs.detail<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
FROM (storeItemsquantities AS quantities LEFT OUTER JOIN basic_rules_specs AS specs ON quantities.itemID=specs.itemID) INNER JOIN concept_matrix AS cmatrix ON quantities.store_no = cmatrix.store_no and cmatrix.24hrs=’N’;<o></o>

FYI: concept_matrix is the table that contains store information. Quantities is the table that contains the "rough" bill of materials.

I have also tried to do the left outer join with the cmatrix table but I get the same result.

Can anyone help me, please? I running out of ideas. If there is a better way to build the query please let me know as well, since I am new to Access and SQL.

Thank you!!

 

[Trevor G]

Welcome to the forum,

It would be handed to see the database or sample but you can't attach a copy through this forum.

Can you upload to a shared facility (Zip it first as most don't like database extensions) so it can be downloaded and explored and hopefully a solution can be found for you.

 

[calcal]

Hi,

Hopefully this link works:

https://rapidshare.com/files/3753977373/Short_term_solution_BOM_database.zip

This is a dummied down version of the DB I am working on, but hopefully illustrates what I am trying to do. I am looking to create a mini-engine that, based on predefined rules, modifies my initial Bill of Materials for a store by either adding specs to items I need to order, by adding more items (e.g., if I am ordering this item I need to order this other as well) or splitting some other items (e.g., if I need a PC I am really ordering a CPU and a monitor). All those rules are based on conditions such as store open hours, store location, store concept, etc. (I removed some of those fields to make the sample DB easier to understand)

Thank you very much in advance! I have had a headache for days because of this!