Access Developer Network

Query Problem in my DB Upload Images Link
Dwayne Squires
4 months ago

I have a query that will only show 1 record for some reason. All other records come back blank. I cannot figure out why only one record will populate on the table. I have a similar query with slightly less information that works perfect.

I need someone more experienced to review and help me with this problem.

Thank you,
Dwayne

Scott Axton
4 months ago

Usually if I have a complicated query I break it down to the most basic then add in criteria one at a time.
It's hard to say where your issue is. Feel free to switch to SQL view and copy your query then paste it in to a post here.
Could be that you are missing quotes, parenthesis, mistyped field names, could be any number of things.

Dwayne Squires
4 months ago

Scott,

Here is the SQL view of the Query below. I assumed this was a paid request so curious about that as you seem to be willing to check it out without bringing that up. I will try your method of adding each item one at a time. In hindsight this seems like a logical course of action, and I should have tried that first. Please let me have some time to use this method and get back to you before you spend any time reviewing this. I can start with the Query that already works and go from there. As you can see I am pulling from multiple tables (but have never had a problem with this before). My basic query that is working draws from multiple tables as well.

Code:
SELECT ServicesT.ServiceID, ServicesT.ServiceType, ServiceCategoryT.ServiceCategoryName, VendorT.VendorName, VendorPOCT.VendorPOCLastName, CALNETPOCT.CPOCLastName, ServicesT.IsActive, PricingCategoryT.PricingCategory, BillingPeriodT.BillingPeriod, ServicesT.StartDate, ServicesT.EndDate, ServicesT.AutoRenewal, ServicesT.Contract, ServicesT.ContractFileName, ServicesT.Comments, ServicesT.ChargedtoCard, CreditCardT.CreditCardName, ServicesT.ChangedVendor, ServicesT.WhyChange, ServicesT.ChangeDate, ServicesT.ChangedTo

FROM VendorT INNER JOIN (CALNETPOCT INNER JOIN (((BillingPeriodT INNER JOIN (PricingCategoryT INNER JOIN (ServiceCategoryT INNER JOIN ServicesT ON ServiceCategoryT.ServiceCategoryID = ServicesT.ServiceCategoryID) ON PricingCategoryT.PricingCategoryID = ServicesT.PricingCategoryID) ON BillingPeriodT.BillingPeriodID = ServicesT.BillingPeriodID) INNER JOIN CreditCardT ON ServicesT.CreditCardID = CreditCardT.CreditCardID) INNER JOIN VendorPOCT ON ServicesT.VendorPOCID = VendorPOCT.VendorPOCID) ON CALNETPOCT.CALNETPOCID = ServicesT.CALNETPOCID) ON (VendorT.VendorID = VendorPOCT.VendorID) AND (VendorT.VendorID = ServicesT.VendorID);

Thank you,
Dwayne

Dwayne Squires
4 months ago

Scott,

I have made some progress. I created from scratch a simplified query with only 3 fields. However, I brought in all the tables even though I was not going to use them all right away. This new query had the same problem of showing only one record right off the bat.

So, the problem has to be the tables and the relationships between all the tables. I do have one table that is related to two other tables that are also related to each other. This is creating sort of a circular relationship. I have a feeling that this may be the problem. But have to test that theory. Again, once I have the answer, I will let you know. Thank you for the troubleshooting methodology tip.

Just for your reference here are the two query codes. I am not an SQL expert, so the codes do not mean much to me. I understand where they come from and sort of how they are generated but I can't just read the code and see what is what.

Good Query code:
SELECT ServicesT.ServiceID, VendorT.VendorName, ServicesT.ServiceType, ServiceCategoryT.ServiceCategoryName, PricingCategoryT.PricingCategory, BillingPeriodT.BillingPeriod, ServicesT.StartDate, ServicesT.EndDate, ServicesT.AutoRenewal, ServicesT.Contract

FROM VendorT INNER JOIN (ServiceCategoryT INNER JOIN (PricingCategoryT INNER JOIN (BillingPeriodT INNER JOIN ServicesT ON BillingPeriodT.BillingPeriodID = ServicesT.BillingPeriodID) ON PricingCategoryT.PricingCategoryID = ServicesT.PricingCategoryID) ON ServiceCategoryT.ServiceCategoryID = ServicesT.ServiceCategoryID) ON VendorT.VendorID = ServicesT.VendorID

ORDER BY VendorT.VendorName, ServiceCategoryT.ServiceCategoryName;

Bad Query Code:
SELECT ServicesT.ServiceID, ServicesT.ServiceType, ServicesT.ServiceCategoryID

FROM (BillingPeriodT INNER JOIN (VendorT INNER JOIN (VendorPOCT INNER JOIN (PricingCategoryT INNER JOIN (ServiceCategoryT INNER JOIN ServicesT ON ServiceCategoryT.ServiceCategoryID = ServicesT.ServiceCategoryID) ON PricingCategoryT.PricingCategoryID = ServicesT.PricingCategoryID) ON VendorPOCT.VendorPOCID = ServicesT.VendorPOCID) ON (VendorT.VendorID = VendorPOCT.VendorID) AND (VendorT.VendorID = ServicesT.VendorID)) ON BillingPeriodT.BillingPeriodID = ServicesT.BillingPeriodID) INNER JOIN CreditCardT ON ServicesT.CreditCardID = CreditCardT.CreditCardID;

Dwayne Squires
4 months ago

Scott,

Ok. Very surprising results to me. It appears that when I add the CreditCardT, this is when the error occurs. The one record that shows up is the only record that has a CreditCardID number. All the other records have a zero since no CreditCard information was added for the other services. I didn't catch that the only record appearing was the only one with creditcard information. It appears that because of the zero value in my main ServicesT which only has a relational numbered field. I went back and reviewed that table and discovered that somehow the Default value had been set to zero. I removed that and fixed all the records to be empty fields. However, that did not solve the problem.

Furthermore, looking at other tables I entered I now realize that any record that has a zero value number that is related to another table's ID autonumber is also not showing up on the query. So, this is the real problem. If I have a "related table" such as VendorPOCT and yet that Vendor does not have a POC. Then the query is not ever going to show that record because there is no relational number to other table. Instead of just showing a blank field, it doesn't show the record at all. I also noticed that all my relational number fields apparently are set to default to zero if nothing is selected from the ServicesT's ServicesF combobox. How I have never noticed this before is crazy. So, I did not realize Queries would just leave out records if a "relational" number is blank or zero. I built two other databases and never noticed this problem. All of my relational fields to other ID fields in other tables are set up the same with Zero default values. It appears this just may be the first time I have blanks in the relational fields. I must be missing an instruction on how to deal with this issue. For example, not all Vendors have specific POCs. Some only have Toll free Costumer Service numbers. For this example, I could just have that as a POC and make sure they pick something. But the Credit Card table is another problem. As very few of our Vendors use our credit cards for payments. Most send us invoices. Is there a way to deal with having blanks in your relational fields? In my ServicesF, I have a Yes/No Creditcard field. If that is not marked Yes, then the other fields for that table will not even appear on my form to select from. LOL Any advice on how to deal with this issue?

Kevin Robertson
4 months ago

Outer Joins

Dwayne Squires
4 months ago

Awesome, Thank you Gentlemen.

Dwayne Squires
4 months ago

Outer Joins helped and caused problems. Just wanted to share as you may have someone else that has this problem.

In my query I had two tables that were causing records not to show up.  The one I mentioned above CreditCardT which I noticed first because it caused only 1 record to show up. However, I had a second table causing records not to show up and that was VendorPOCT. I tried to use Left Joins (or Left Outer Joins) on both tables. However, I got an error message stating that I had ambiguous Outer Joins and to create one query doing the Outer Join I wanted first and then to copy that SQL code into another query.  Using my newfound troubleshooting technique (recommended by Scott). I quickly discovered that deleting the CardCardT table did not solve the problem. I still got the error message. Well, if you look back in my second reply to Scott, I mentioned that I had a table related to two other tables. Creating a sort of circular loop between my Base Table (ServicesT) and two supporting tables (VendorT and VendorPOCT).

Experimenting I broke the link between VendorT and VendorPOCT.  This stopped the error message from appearing and my query worked. Then continuing to experiment I added my CreditCardT back in and walla, my query still worked with two Left Outer Joins. Now, I finally have the query I was trying to get in the first place.

Again, thank you for the help. I know it may not have seemed like much, but it was all I needed.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Access Developer Network Comments

Subscribe to Access Developer Network
Get notifications when this page is updated