Joining 3 tbls in a qry, help getting info only found in 2 of the tbls

[chrismja]

I am joining 3 tables, but it's only pulling matching records in the tables, and there are some unique records I also need it to pull. I'm sure there is a way to do this, but I don't know how. Here's the situation: I have a table named "Total Inventory" and I have 2 other tables that include incentives for 2004 models and 2005 models. I have a query that that has these 3 tables joined via joining "2004 incentives.body code" to "Total Inventory.Model Code" (which are the same thing), then on the other side I also have 2005 incentives table joined to "Total Inventory" via "2005 Incentives.body code" to "Total Inventory.Model Code" . My problem is this: The query pulls all the records from inventory that has a matching model code to the "body codes" in the 2 incentive tables with the exception of "body codes" that are only listed in either the 2004 or 2005 incetive tables. In other words the "body code" must be listed in both 2004 and 2005 Incentive tables in order for the query to pull the matching records. However there are some "body codes" that are only found in either the 2004 or in the 2005 incentive table. How can I make this query pull all of the records that have a match in "Total Inventory" if they don't have a match in the other incentive table? I've already tried doing different joins such as left join and right join, but get an error meassge: the SQL statement could not be executed because it contains ambiguous outer joins. Does anyone have any suggestions?

 

[traingamer]

Try two independent left joins like:

...From [Total Inventory] Left Join [2004 incentives] on [Total Inventory].[Model Code] = [2004 incentives].[body code] Left Join [2005 incentives] on [Total Inventory].[Model Code] = [2005 incentives].[body code] ...

That should get all records from Total and all matching recs from either of the other tables.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[chrismja]

Thanks! I appreciate your help! Maybe you can tell me what I'm still doing wrong, I tried what you suggested, and this is what it looks like:
FROM [TOTAL INVENTORY] LEFT JOIN [TOWNCAR4] ON [TOTAL INVENTORY].[MODEL] = [TOWNCAR4].BODY LEFT JOIN [TOWNCAR5] ON [TOTAL INVENTORY].MODEL = [TOWNCAR5].BODY

but when I try to run it, it gives me an error:"Syntax error (missing operator) in query expression '[TOTAL INVENTORY].MODEL = [TOWNCAR4].BODY LEFT JOIN [TOWNCAR5] ON [TOTAL INVENTORY].MODEL = [TOWNCAR5].BODY'

Do you see what my problem might be?

 

[traingamer]

Try this one:

FROM [COLOR=red]([/color][TOTAL INVENTORY] LEFT JOIN [TOWNCAR4] ON [TOTAL INVENTORY].[MODEL] = [TOWNCAR4].BODY[COLOR=red])[/color] LEFT JOIN [TOWNCAR5] ON [TOTAL INVENTORY].MODEL = [TOWNCAR5].BODY

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[chrismja]

I don't know if you may have any other suggestions....it is working partially... It is pulling some of the records just fine, but then in other records where the field includes an expression that I have built (and was working fine)it just says "error".

 

[traingamer]

I'm guessing that your expression is referencing a null field, but can't tell for sure unless you post it. What do you mean by "was working"?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[chrismja]

I really appreciate all your help. Ok....let me see if I can explain. I work for a Ford dealership, and I have a spreadsheet that I use to price our vehicles (instead of negotiating we choose to give customers our best price upfront) I think Access can do what my spreadsheet is doing and be more efficient at it. What I've got is an expression in a query that looks at "Total Inventory" (which I import from a spreadsheet) I have tables that include the current incentives for 2004 model vehicles and a separate table with 2005 incentives. What I've got my expression doing is matching "body codes" in the incentive tables to "model codes" in "Total Inventory", so the expression is only pulling matching "body" and "model" codes, so in other words it would only pull the vehicles out of Total Inventory that matched the incentive tables for the vehicle that I'm building the query for. My expression then looks to see if it's a 2004 and uses those incentives if so, or if a 2005 then it uses the incentives for 2005. What I meant by "it was working" was that my expression was doing all my calculations fine and pulling the vehicles into the query, but I noticed that if there was a "body code" in the 2005 or 2004 incentive tables that wasn't in the other, it wouldn't pull out those vehicles. It was only working and pulling out the vehicles that has matching "body codes" (or "model codes") in all 3 of my tables that I'm basing my query off of, which would be Total Inventory, 2004 Incentives (for a specific vehicle), and 2005 Incentives (for that same vehicle). I would be happy to email you a sample if you think it would help you to help me!!

 

[traingamer]

Can you post the expression that isn't working? Someone here can probably get it to work.

On the other hand, if you just had just ONE Incentives table with an additional field for YEAR, you could probably make your life (and your query) a LOT simpler.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

you may also want to read The Fundamentals of Relational Database Design for more information on designing databases.

Leslie

 

[chrismja]

Here is my expression: PRICE: IIf([TOTAL INVENTORY]![YR]=4,DLookUp([TOWNCAR4]![%],"TOWNCAR4",[TOTAL INVENTORY]![MODEL]=[TOWNCAR4]![BODY])*([TOTAL INVENTORY]![INV $$$])-(DLookUp([TOWNCAR4]![TOTAL],"TOWNCAR4",[TOTAL INVENTORY]![MODEL]=[TOWNCAR4]![BODY])),IIf([TOTAL INVENTORY]![YR]=5,DLookUp([TOWNCAR5]![%],"TOWNCAR5",[TOTAL INVENTORY]![MODEL]=[TOWNCAR5]![BODY])*([TOTAL INVENTORY]![INV $$$]))-(DLookUp([TOWNCAR5]![TOTAL],"TOWNCAR5",[TOTAL INVENTORY]![MODEL]=[TOWNCAR5]![BODY])))

There may be a way to fix my problem (only pulling vehicles with "body codes" that are in all 3 tables) in my expression, but I'm not sure how it can be fixed. The way you suggested that I fix my SQL statement works in one direction, but the only problem is that if there are unique "body codes" in both of my incentive tables, it only looks at the first left join....and doesn't pull any unique "body codes" from the second left join. There may be no other way to correct this besides putting all my incentives all in one table together. I had already considered doing this, the reason I decided against it was because I'm not the person who updates our incentives in these tables, and I have been afraid of them not paying attention with it all in one table and could end up putting 2004 incentives in the 2005 place or vise versa. Then we might end up with alot of vehicle priced wrong, which could cost us alot of business or alot of money and either outcome isn't a good one! So maybe you see my predicament!

 

[chrismja]

I have put all my incetives in one table with an extra field for the year. Now what I'm trying to figure out is how to rebuild my expression and make it lookup the year and then the model to match up the incentives. This is my current expression:
PRICE: IIf([TOTAL INVENTORY]!YR=4,DLookUp(FOCUS4![%],"FOCUS4",[TOTAL INVENTORY]!MODEL=FOCUS4!BODY)*([TOTAL INVENTORY]![INV AMT])-(DLookUp(FOCUS4!TOTAL,"FOCUS4",[TOTAL INVENTORY]!MODEL=FOCUS4!BODY)),IIf([TOTAL INVENTORY]!YR=5,DLookUp(FOCUS5![%],"FOCUS5",[TOTAL INVENTORY]!MODEL=FOCUS5!BODY)*([TOTAL INVENTORY]![INV AMT]))-(DLookUp(FOCUS5!TOTAL,"FOCUS5",[TOTAL INVENTORY]!MODEL=FOCUS5!BODY)))

Now, however there will not be a FOCUS4 or FOCUS5 which were my 2 separate tables, it will now be only one table which I named TOTAL_FOCUS. I guess what I'm needing to know would be is there a way to include an "AND" in my DLOOKUP so I can tell it if the vehicle is a 2004, to lookup the vehicles body code that matches the body code in incentives, but at the same time needs to make sure the year matches....

 

[lespaul]

can you post the rest of your SQL, not just this single criteria?

Leslie

 

[chrismja]

Here you go!!! I appreciate both of your help VERY MUCH!!!!

SELECT [TOTAL INVENTORY].[RECD DTE], [TOTAL INVENTORY].[STOCK NO], [TOTAL INVENTORY].YR, [TOTAL INVENTORY].CARLINE, [TOTAL INVENTORY].[SERIAL#], [TOTAL INVENTORY].MODEL, [TOTAL INVENTORY].DAY, [TOTAL INVENTORY]., [TOTAL INVENTORY].[LST PRICE], [TOTAL INVENTORY].[INV AMT], IIf([TOTAL INVENTORY]!YR=4,DLookUp(FOCUS4![%],"FOCUS4",[TOTAL INVENTORY]!MODEL=FOCUS4!BODY)*([TOTAL INVENTORY]![INV AMT])-(DLookUp(FOCUS4!TOTAL,"FOCUS4",[TOTAL INVENTORY]!MODEL=FOCUS4!BODY)),IIf([TOTAL INVENTORY]!YR=5,DLookUp(FOCUS5![%],"FOCUS5",[TOTAL INVENTORY]!MODEL=FOCUS5!BODY)*([TOTAL INVENTORY]![INV AMT]))-(DLookUp(FOCUS5!TOTAL,"FOCUS5",[TOTAL INVENTORY]!MODEL=FOCUS5!BODY))) AS PRICE, [TOTAL INVENTORY]![LST PRICE]-[PRICE] AS SAVINGS, IIf([TOTAL INVENTORY]!DAY<=3,"NEW UNIT") AS FLAG
FROM FOCUS5 INNER JOIN (FOCUS4 INNER JOIN [TOTAL INVENTORY] ON FOCUS4.BODY = [TOTAL INVENTORY].MODEL) ON FOCUS5.BODY = [TOTAL INVENTORY].MODEL
ORDER BY [TOTAL INVENTORY].[RECD DTE], [TOTAL INVENTORY].[STOCK NO];

 

[lespaul]

does the table TOTAL INVENTORY contain the year as well? If so, this may work: (I'm not sure I got your calculation right!!)

SELECT T.[RECD DTE], T.[STOCK NO], T.YR, T.CARLINE, T.[SERIAL#], T.MODEL, T.DAY, T.[COLOR=DESCRIPTIONS], T.[LST PRICE], T.[INV AMT], (F.BODY * T.[INV AMT] - F.TOTAL) As PRICE, T.[LST PRICE] - (F.BODY * T.[INV AMT] - F.TOTAL) As SAVINGS, IIf([TOTAL INVENTORY]!DAY<=3,"NEW UNIT") AS FLAG
FROM [TOTAL INVENTORY]  T
INNER JOIN [TOTAL FOCUS] F ON F.BODY = T.MODEL AND F.YEAR = T.YEAR
ORDER BY T.[RECD DTE], T.[STOCK NO];

Leslie

 

[chrismja]

Yes, it includes the date as a single digit, "4" for 2004, "5" for 2005. I'll try this and see! Thanks again!

 

[lespaul]

if it only has part of the year you'll need to use this:

SELECT T.[RECD DTE], T.[STOCK NO], T.YR, T.CARLINE, T.[SERIAL#], T.MODEL, T.DAY, T.[COLOR=DESCRIPTIONS], T.[LST PRICE], T.[INV AMT], (F.BODY * T.[INV AMT] - F.TOTAL) As PRICE, T.[LST PRICE] - (F.BODY * T.[INV AMT] - F.TOTAL) As SAVINGS, IIf([TOTAL INVENTORY]!DAY<=3,"NEW UNIT") AS FLAG
FROM [TOTAL INVENTORY]  T
INNER JOIN [TOTAL FOCUS] F ON F.BODY = T.MODEL AND RIGHT(F.YEAR,1) = T.YEAR
ORDER BY T.[RECD DTE], T.[STOCK NO];

Leslie

 

[chrismja]

I typed it in exactly as you had it and I'm getting an error: syntax error: (missing operator) in query expression 'TOTAL INVENTORY.[LST PRICE]-(TOTAL_FOCUS.BODY*TOTAL INVENTORY.[INV AMT]-TOTAL_FOCUS.TOTAL)

Any suggestions? As you can see by my old expression, I do understand how to write them, but to be honost you are a little over my head as to how this one is supposed to work. It looks simple enough, just not sure what it is supposed to mean or how it is supposed to work. I have always worked in Excel mostly, so you may notice my expressions are built like a formula. Obviously, from the looks of your expression though, there must be a better way of doing things than the way I did have it done. Would you mind explaining to me a little about what you are telling the query to do?

 

[chrismja]

Ok...I finally figured out my syntax error, and I'm convinced that this is how my SQL statement should read, but I'm getting a type mismatch error......any suggestions?

SELECT [TOTAL INVENTORY].[RECD DTE], [TOTAL INVENTORY].[STOCK NO], [TOTAL INVENTORY].YR, [TOTAL INVENTORY].CARLINE, [TOTAL INVENTORY].[SERIAL#], [TOTAL INVENTORY].MODEL, [TOTAL INVENTORY].DAY, [TOTAL INVENTORY]., [TOTAL INVENTORY].[LST PRICE], [TOTAL INVENTORY].[INV AMT], (TOTAL_FOCUS.[%]*[TOTAL INVENTORY].[INV AMT]-TOTAL_FOCUS.TOTAL) AS PRICE, [TOTAL INVENTORY].[LST PRICE]-[PRICE] AS SAVINGS, IIf([TOTAL INVENTORY]!DAY<=3,"NEW UNIT") AS FLAG
FROM [TOTAL INVENTORY] INNER JOIN TOTAL_FOCUS ON (TOTAL_FOCUS.BODY=[TOTAL INVENTORY].MODEL) AND (RIGHT(TOTAL_FOCUS.YEAR,1)=[TOTAL INVENTORY].YR)
ORDER BY [TOTAL INVENTORY].[RECD DTE], [TOTAL INVENTORY].[STOCK NO];

 

[chrismja]

I FINALLY figured it out!!!! Thanks in no small part to your help!!!! I really appreciate it!!!

 

[lespaul]

glad to help!

Leslie