creating relationships cause data to not show..

**mike02** · 06-13-2013, 12:25 PM

I am making a new query:

I am creating a relationship from my one table to my queries. there is 6 queries and 1 table.

From the Table it will have a relationship with all 6 queries of:

ClientID=ClientID
MonthlyDate=MonthlyDate

Now the issue i am having is it only dispays the values in the field if I only use 2 of the tables. but as soon as i use more then 2 it leaves those values empty.

Has anyone had this happen before?
Here is my Join SQL:

Code:

FROM (((((Accounts INNER JOIN qryE ON (Accounts.MonthlyDate = qryE.MonthlyDate) AND (Accounts.ClientID = qryE.ClientID)) INNER JOIN qryE ON (Accounts.MonthlyDate = qryF.MonthlyDate) AND (Accounts.ClientID = qryF.ClientID)) INNER 
JOIN qryA ON (Accounts.MonthlyDate = qryA.MonthlyDate) AND (Accounts.ClientID = qryA.ClientID)) INNER JOIN qryB ON (Accounts.MonthlyDate = qryB.MonthlyDate) AND (Accounts.ClientID = qryB.ClientID)) INNER JOIN qryC ON (Accounts.MonthlyDate = qryC.MonthlyDate) AND (Accounts.ClientID = qryC.ClientID)) INNER JOIN qryD ON (Accounts.MonthlyDate = qryD.MonthlyDate) AND (Accounts.ClientID = qryD.ClientID)
ORDER BY Accounts.MonthlyDate;

**JoeM** · 06-13-2013, 12:42 PM

I think you have a typo. It looks like you joined qryE twice instead of qryE and qryF.

Code:

FROM 
(((((Accounts 
	INNER JOIN 
		qryE 
	ON 
		(Accounts.MonthlyDate = qryE.MonthlyDate) 
		AND (Accounts.ClientID = qryE.ClientID)) 
		
	INNER JOIN 
		qryE 
	ON 
		(Accounts.MonthlyDate = qryF.MonthlyDate) 
		AND (Accounts.ClientID = qryF.ClientID)) 
		
	INNER JOIN 
		qryA 
	ON 
		(Accounts.MonthlyDate = qryA.MonthlyDate) 
		AND (Accounts.ClientID = qryA.ClientID)) 
	INNER JOIN 
		qryB 
	ON 
		(Accounts.MonthlyDate = qryB.MonthlyDate) 
		AND (Accounts.ClientID = qryB.ClientID)) 
		
	INNER JOIN 
		qryC 
	ON 
		(Accounts.MonthlyDate = qryC.MonthlyDate) 
		AND (Accounts.ClientID = qryC.ClientID)) 
		
	INNER JOIN 
		qryD 
	ON 
		(Accounts.MonthlyDate = qryD.MonthlyDate) 
		AND (Accounts.ClientID = qryD.ClientID)

ORDER BY 
	Accounts.MonthlyDate;

**mike02** · 06-13-2013, 12:57 PM

It still will not show any data, I have 2 fields that i want to show from each query. 1 field from each of the 6 will show. the second of the 6 will now show.

**JoeM** · 06-13-2013, 01:09 PM

Keep in mind that when doing INNER JOINS in all your relationships, you need to have matching records between ALL 6 sources to return any data. If just one does return data for that the record you expect, it will return nothing.
If you want to return one "master" record, and all the matching records from the other 5, then do LEFT OUTER joins between your "master" table and the other 5 sources.

If you think that there are certain records that appear in ALL 6 sources, but still are not returning anything, break it down. Start with just two sources, and see if it returns data. If it does, add the next source and check it. Continue with this process until it does not return any data. When that happens, focus in on the last join you created. That is the one that "breaks the process", so that is where you will want to focus your attention and investigate the relationship, data, and situation.

**mike02** · 06-13-2013, 02:15 PM

I have tried what you said. And it works just fine for all other fields in the queries, but this one common field (same Expression) does not work. The values in this field are different and it is not the same!

any other possible ways?

**JoeM** · 06-13-2013, 02:31 PM

It is hard to say without seeing your full SQL code, table structure, and data.
Can you post the complete SQL code, and tell us which field is having issues?
It might be helpful to post the whole database, if possible (after removing any confidential information).

**mike02** · 06-13-2013, 02:57 PM

Here is my SQL, let me know if it helps, if not i can create a template for example and post:

Code:

SELECT Accounts.ClientID, Accounts.ProductID, Accounts.MonthlyDate, (SELECT MAX(T3.MonthlyDate) 
    FROM Accounts AS T3
    WHERE T3.ClientID = Accounts.ClientID
    AND T3.MonthlyDate < Accounts.MonthlyDate) AS PriorDate, (SELECT Sum(T2.[Cash]) 
    FROM Accounts AS T2
    WHERE T2.ClientID = Accounts.ClientID
    AND T2.MonthlyDate <= Accounts.MonthlyDate) AS NetCash, 
 
[StartingAssetFormula NotCreatedYet] AS [Starting Assets], 

 ([TransferIN]+[TransferOut]+[Fees]+[Transactions]+[Interest]+[NetCash]) AS Cash, 

 ([qryA].[EOMAssetValue]+[qryB].[EOMAssetValue]+[qryC].[EOMAssetValue]+[qryD].[EOMAssetValue]+[qryE].[EOMAssetValue]+[qryF].[EOMAssetValue]) AS [COB Assets], 

 [COB Assets]/[Starting Assets]-1 AS [Net Return], ([COB Assets]-[Fees])/[Starting Assets]-1 AS [Gross Return], 

 Accounts.IncludeComposite, 
 
 [COB Assets]-[Starting Assets]-[TransferIN] AS [Comp Return Net], 

 [Comp Return Net]-[Fees] AS [Comp Return Gross], 

 Accounts.TransferIn, 

 Accounts.TransferOut, 

 -([qryA].[Assets Add/Sub]+[qryB].[Assets Add/Sub]+[qryC].[Assets Add/Sub]+[qryD].[Assets Add/Sub]+[qryE].[Assets Add/Sub]+[qryF].[Assets Add/Sub]) AS Transactions, 

 Accounts.Fees, Accounts.Interest, qryB.[Assets Add/Sub], qryB.[Assets Add/Sub], qryC.[Assets Add/Sub], qryD.[Assets Add/Sub], qryE.[Assets Add/Sub], qryF.[Assets Add/Sub], qryA.EOMAssetValue, qryB.EOMAssetValue, qryC.EOMAssetValue, qryD.EOMAssetValue, qryE.EOMAssetValue, qryF.EOMAssetValue 
FROM (((((Accounts 	INNER JOIN 		qryE 	ON 		(Accounts.MonthlyDate = qryE.MonthlyDate) 		AND (Accounts.ClientID = qryE.ClientID)) 			INNER JOIN 		qryE 	ON 		(Accounts.MonthlyDate = qryF.MonthlyDate) 		AND (Accounts.ClientID = qryF.ClientID)) 			INNER JOIN 		qryA 	ON 		(Accounts.MonthlyDate = qryA.MonthlyDate) 		AND (Accounts.ClientID = qryA.ClientID)) 	INNER JOIN 		qryB 	ON 		(Accounts.MonthlyDate = qryB.MonthlyDate) 		AND (Accounts.ClientID = qryB.ClientID)) 			INNER JOIN 		qryC 	ON 		(Accounts.MonthlyDate = qryC.MonthlyDate) 		AND (Accounts.ClientID = qryC.ClientID))

EOMAssetValues are the fields not showing.
Assets Add/Sub are the fields that are showing.
My subquery isnt working yet either, so ignore that part.

Thanks,

**JoeM** · 06-14-2013, 06:08 AM

That's a bit more complex than I envisioned. I don't know how much I could with that without seeing the database, specifically so we can see the structure/layout of your tables and data.

**mike02** · 06-14-2013, 08:46 AM

Im going to try and rebuild everything. see how that works. When i do your error test, they all will show numbers, but only 2 at a time. if i add the third query, either qryA,B,C,D,E,F IT shows no value.

**mike02** · 06-14-2013, 11:45 AM

could there be a setting issue? or what are possible causes of some fields showing data and some show nothing?

**mike02** · 06-14-2013, 12:49 PM

Here is an attached copy

**JoeM** · 06-16-2013, 06:59 AM

Your PriorDate and NetCash calculations look curious to me. I am not sure what you are trying to do with them. I am not sure the calculations are done correctly. Can you explain the logic you are trying to use?

Let's take a step back. Let's remove all calculations from your summary query. That SQL coude would look something like this:

Code:

SELECT Accounts.ClientID, Accounts.ProductID, Accounts.MonthlyDate, Accounts.IncludeComposite, Accounts.TransferIn, Accounts.TransferOut, Accounts.Fees, Accounts.Interest, Accounts.Dividends, qryE.[Assets Add/Sub], qryF.[Assets Add/Sub], qryD.[Assets Add/Sub], qryC.[Assets Add/Sub], qryB.[Assets Add/Sub], qryA.[Assets Add/Sub], qryA.EOMAssetValue, qryB.EOMAssetValue, qryC.EOMAssetValue, qryD.EOMAssetValue, qryE.EOMAssetValue, qryF.EOMAssetValue
FROM (((((Accounts 
INNER JOIN qryA 
ON (Accounts.MonthlyDate = qryA.MonthlyDate) AND (Accounts.ClientID = qryA.ClientID)) 
INNER JOIN qryB 
ON (Accounts.MonthlyDate = qryB.MonthlyDate) AND (Accounts.ClientID = qryB.ClientID)) 
INNER JOIN qryC 
ON (Accounts.MonthlyDate = qryC.MonthlyDate) AND (Accounts.ClientID = qryC.ClientID)) 
INNER JOIN qryD 
ON (Accounts.MonthlyDate = qryD.MonthlyDate) AND (Accounts.ClientID = qryD.ClientID)) 
INNER JOIN qryE 
ON (Accounts.MonthlyDate = qryE.MonthlyDate) AND (Accounts.ClientID = qryE.ClientID)) 
INNER JOIN qryF 
ON (Accounts.MonthlyDate = qryF.MonthlyDate) AND (Accounts.ClientID = qryF.ClientID)
WHERE (((Accounts.ClientID)=1));

Does that at least pull all the records and fields you are expecting?

**mike02** · 06-18-2013, 11:31 AM

It pulls all records acccept all EOM AssetValues.... the value is not NULL either, as I tried using this formula.... NZ(EOMAssetValue,0) but no luck... it still will not pull any records, or leave a 0 value...

**mike02** · 06-18-2013, 12:24 PM

now, I have re done it again, and no it returns a a 0 value with the NZ function... If anyone has had an issue with queries, only givin one set of data when you have two fields selected from a query (for all 6 queries) and only one of the fields returns data... IE>

Assets Add/Sub......EOMAssetValue
Returns all data......Leaves empty

Both fields are formated currency in the query they come from. both expressions. both return numbers in their respective queries.

When tested with something like this: [qryA].[EOMAssetValue]+2
It returns nothing.

Completely confused,

**JoeM** · 06-18-2013, 09:13 PM

I wonder if adding in an extra set of parentheses may help to the code in my last post will help, i.e.

Code:

SELECT Accounts.ClientID, Accounts.ProductID, Accounts.MonthlyDate, Accounts.IncludeComposite, Accounts.TransferIn, Accounts.TransferOut, Accounts.Fees, Accounts.Interest, Accounts.Dividends, qryE.[Assets Add/Sub], qryF.[Assets Add/Sub], qryD.[Assets Add/Sub], qryC.[Assets Add/Sub], qryB.[Assets Add/Sub], qryA.[Assets Add/Sub], qryA.EOMAssetValue, qryB.EOMAssetValue, qryC.EOMAssetValue, qryD.EOMAssetValue, qryE.EOMAssetValue, qryF.EOMAssetValue
FROM ((((((Accounts 
INNER JOIN qryA 
ON (Accounts.MonthlyDate = qryA.MonthlyDate) AND (Accounts.ClientID = qryA.ClientID)) 
INNER JOIN qryB 
ON (Accounts.MonthlyDate = qryB.MonthlyDate) AND (Accounts.ClientID = qryB.ClientID)) 
INNER JOIN qryC 
ON (Accounts.MonthlyDate = qryC.MonthlyDate) AND (Accounts.ClientID = qryC.ClientID)) 
INNER JOIN qryD 
ON (Accounts.MonthlyDate = qryD.MonthlyDate) AND (Accounts.ClientID = qryD.ClientID)) 
INNER JOIN qryE 
ON (Accounts.MonthlyDate = qryE.MonthlyDate) AND (Accounts.ClientID = qryE.ClientID)) 
INNER JOIN qryF 
ON (Accounts.MonthlyDate = qryF.MonthlyDate) AND (Accounts.ClientID = qryF.ClientID))
WHERE (((Accounts.ClientID)=1));

If you are still not getting anything to return, try to do a relationship just between the original table and qryF, i.e.

Code:

SELECT Accounts.ClientID, Accounts.ProductID, Accounts.MonthlyDate, Accounts.IncludeComposite, Accounts.TransferIn, Accounts.TransferOut, Accounts.Fees, Accounts.Interest, Accounts.Dividends, qryF.[Assets Add/Sub], qryF.EOMAssetValue
FROM Accounts 
INNER JOIN qryF 
ON (Accounts.MonthlyDate = qryF.MonthlyDate) AND (Accounts.ClientID = qryF.ClientID)
WHERE Accounts.ClientID=1;

Does that return the values from qryF that you expect?

creating relationships cause data to not show..

Thread Tools

creating relationships cause data to not show..

Similar Threads

Creating Relationships between tables

Relationships required for creating forms

Creating a Report and the rpt fields are based on key relationships.

show table relationships

Creating Relationships and Queries

Posting Permissions