Help identifying access problems, please

[JNana]

Can anyone direct me in creating some query, etc. to locate a problem that I
am having? I have an Order Entry database. This has been used for
approximately 1 year. The reports in question have been functioning
properly. Now, for some unknown reason, I am not getting all of the
information. All of the fields look the same.

My reports are using the following tables:
Contracts - 3 different contract #s; one or all can be specific to an order.

SIN - each product has a SIN (special item number); each SIN pertains to a
specific contract.

Dealer - company delivering order

Order - summary listing customer name, billto info, orderID, shipdate,total
of order, payments, balance

OrderDetails - line item, productcode, product name,serial number, quantity,
unit price, extended price, notes

Products - items available for sale with description, codes, unit prices,
contract #, SIN #.


My reports are Invoice, Bill of Lading, Packing List, Authorization to
Deliver, and Dealer Daily Delivery Info.

My first problem was that line items 5 and 6 would display on the order
details screen, but would not print on any report. The only way I
temporarily solved this was to remove the Contracts and SIN tables from the
query. I still need this on my reports.

Now, my Dealer Daily Delivery Info report displays for some customers and
does not display for others. Customers prior to my deleting the Contracts
and SIN tables all display and print okay. I did not run this report when
the first problem appeared.

How do I go about searching for the problem? I have looked at each
customer - the info does not appear to be any different. The products look
like they have all the information. My reports are based upon control
number which relates to a specific contract. If it were this relationship
being the problem, why would some of the information be okay and others
not??

The query does the same thing also. I can go to the tables and the
information is there.

I don't understand why this has suddenly appeared and why some display and
others do not.

Thank you in advance for any assistance you give.

JNana

 

[Guest]

Hi JNana,

Have you created relationships between the tables (Tools > Relationships)?
If yes, do your relationships include having referential integrity (RI)
enforced (very important)?

I suspect that you have either unmatched foreign key values (in which case,
an attempt to enforce RI will fail) or you have no entries in the foreign key
fields, and these fields do not include Required = Yes as a field property.
The default join between two tables is an inner join. Inner joins will return
records only when there are matching values in both tables.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[JNana]

Tom -

Thank you for replying. I am really not very adept at Access. I muddle my
way through, but am really in need of help here.

I have created relationships. They do have RI. I did not create foreign
key values (?) or did I?

Question please: If my form, Order Details displays all the information and
if the table contains all of the information, why do my reports not contain
all of the information? One of my orders is dropping the first and second
line item; another is dropping 5 and 6 line items; and still others are not
displaying any information. The reports are using the same tables that were
used for the order/order details forms.

Perhaps I should just attempt to create new reports. Any suggestions are
appreciated.
JNana

 

[Guest]

Hi JNana,

I have created relationships. They do have RI. I did not create foreign
key values (?) or did I?

Yes, you did. A foreign key is simply a primary key from another table. So,
for example, if you have a field in your Orders table that is the CustomerID
value, then this would be considered the foreign key.

why do my reports not contain all of the information?

Hard to say without looking at your database. What is the recordsource for
your report? Is it a table or a query? To determine the recordsource, open
the report in design view. Click on View > Properties if required to display
the properties dialog. You should see "Report", in the blue title bar of the
properties dialog. If you see anything else, then click on the small black
square in the upper left corner to select the report. Select the Data tab.
The first entry reads "Record Source". Click on the build button (the button
with the three dots that should become available, as soon as you click into
the record source). If your record source was a table, the query builder
wizard will respond with a message indicating so. If it was a query, then the
wizard will simply open the query. Run the query. Do you get the expected
records returned?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi JNana,

I just went back and re-read your original message. You indicated that your
reports are based on (6) tables: Contracts, SIN, Dealer, Order, OrderDetails
and Products. You also gave a crucial clue:

"The only way I temporarily solved this was to remove the
Contracts and SIN tables from the query."

I'm willing to bet that you are missing a ContractID and/or SinID value from
a related table. Does this query rely on the default inner joins? An inner
join will not display an arrowhead in query design view. Also, if you click
on View > SQL View, when looking at the query in design view, do you see the
keywords INNER JOIN? If so, then the query will return records only if there
are matching values in both tables involved in the relationship. The fact
that you can drop one or more tables, and then the missing records show up,
provides pretty convincing evidence of missing foreign key values.

Try the following experiment in the sample Northwind database
(Northwind.mdb), which you likely already have a copy on your hard drive.
Search for this file. Make a copy of it and place the copy in a convenient
folder, such as My Documents. Run the Alphabetical List of Products report.
Notice that there is one product that starts with "A" and one product that
starts with "B". Close the report. Open the Products table. Delete the value
"Condiments" shown in the Category field for Aniseed Syrup. Note: This field
has a caption of "Category" defined in table design view; this is why you do
not see "CategoryID". Find the record for Boston Crab Meat in the same table.
Blank out the value of "Seafood" shown in the Category field. You are now
missing two foreign key values in the Category field. Re-run the report.
Notice that you are now missing the data for products that start with "A" and
"B". I think you have a similar situation going on with your database.

The problem is most likely in the query that serves as the record source for
the report, not in the report itself.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[JNana]

Tom -
I did as you suggested and looked at the Northwind Database. I then went
back to my database and found the following regarding relationships. I did
not design this database, but I "think" that I understand. I question why
there were so many different tables created when I compare it to at the
Northwind Database.

Table: ContractControlNum - RI Many to one - ContractID in Contracts Table
Table: ContractSIN XRef - RI Many to one - ContractID in Contracts Table
Table: ContractSIN XRef - RI Many to one - SIN - SIN Table
Table: Contracts - RI - One to many - ContractID in ContractControlNum,
ContractSIN XREF, OrderContractControl
Table: SIN - RI - One to Many - SINID in ContractSIN XREF
Table: OrderContractControl - Many to 1 - OrderID in Orders Table
Table: OrderDetails - RI - OrderID - many to 1 - in Orders Table
Table: Products - RI - ProductID - 1 to many- in OrderDetails table.

Having said all of that, I went back to an older model of this database and
noticed that the Products table had RI with the Contracts Table (via
ContractID) and the SIN Table (via SINID). I attempted to create that
relationship and could not create the RI, but could create it without RI.
The message said I was missing something in the primary table, Products. I
do have ContractID and SINID in that table so I am again at a loss.

I am continuing my research. If you see something that I do not, I would
appreciate your very valuable advise.

Thank you for taking the time to assist.

JNana

 

[JNana]

Following is the SQL from the Report:

SELECT Orders.*, [Order Details].OrderDetailID, [Order Details].ProductID,
Products.ProductCode, [Order Details].Quantity, [Order Details].UnitPrice,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.[Country/Region],
Customers.PhoneNumber, Customers.PONumber, Customers.ContactFirstName & " "
& Customers.ContactLastName AS [Contact Name], [Shipping
Methods].ShippingMethod, Products.ProductName, [Order Details].LineItem,
[Order Details].SerialNum, [Order Details].Discount, Customers.ControlNum,
Dealer.DealerName, [Order Details].Notes
FROM (Dealer RIGHT JOIN (Customers RIGHT JOIN ([Shipping Methods] RIGHT JOIN
(Employees RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON
[Shipping Methods].ShippingMethodID = Orders.ShippingMethodID) ON
Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID =
Orders.DealerID) RIGHT JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products
ON SIN.SINID = Products.SINID) ON Contracts.ContractID =
Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID =
[Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.ShipDate;

I discovered something else - I imported the Products table from the
previous database with RI. This will not work. In searching for the
difference, I discovered that the products table that I am using that will
provide the report info (with RI on Contracts and SIN), when previewed, DOES
NOT display the ProductID; however, the imported table does. Why? I see no
difference in the properties at the bottom of the table form.

Sorry for so much confusing info.

 

[Guest]

In searching for the

difference, I discovered that the products table that I am using that will
provide the report info (with RI on Contracts and SIN), when previewed, DOES
NOT display the ProductID; however, the imported table does. Why?

I don't know, since I cannot see what you have available. I noticed some
RIGHT JOIN's between tables in your query, but I also noticed several INNER
JOIN's. The inner joins will require matching records in both tables, in
order to return anything. I suggest that you test your Contracts and SIN
tables with the Products table, ie.:

SIN.SINID = Products.SINID
and
Contracts.ContractID = Products.ContractID

To find the offending records (those missing a value in the foreign key
field), you can use the "Find Unmatched Query Wizard". Click on Queries in
the database window. Then click on the New button. Select the last item in
the list "Find Unmatched Query Wizard". Select the "many" side table in first
screen, for example the Orders table in Northwind. Select the "one" side
table in the next screen, for example Customers in Northwind. In the next
screen, select the appropriate fields that are used to link the tables.
Follow the rest of the prompts.

From earlier message:

Having said all of that, I went back to an older model of this database and
noticed that the Products table had RI with the Contracts Table (via
ContractID) and the SIN Table (via SINID). I attempted to create that
relationship and could not create the RI, but could create it without RI.

The Find Unmatched Query Wizard will help you locate the offending records
in this case as well.

If you are able to send me a copy of your database, I will take a look at it
for you. Please compact it first (Tools > Database Utilities > Compact and
repair database), and add it to a .zip archive file if you are familiar with
this process. My e-mail address is available at the bottom of the
contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Following is the SQL from the Report:

SELECT Orders.*, [Order Details].OrderDetailID, [Order Details].ProductID,
Products.ProductCode, [Order Details].Quantity, [Order Details].UnitPrice,
Customers.CompanyName, Customers.ContactFirstName,
Customers.ContactLastName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.[Country/Region],
Customers.PhoneNumber, Customers.PONumber, Customers.ContactFirstName & " "
& Customers.ContactLastName AS [Contact Name], [Shipping
Methods].ShippingMethod, Products.ProductName, [Order Details].LineItem,
[Order Details].SerialNum, [Order Details].Discount, Customers.ControlNum,
Dealer.DealerName, [Order Details].Notes
FROM (Dealer RIGHT JOIN (Customers RIGHT JOIN ([Shipping Methods] RIGHT JOIN
(Employees RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON
[Shipping Methods].ShippingMethodID = Orders.ShippingMethodID) ON
Customers.CustomerID = Orders.CustomerID) ON Dealer.DealerID =
Orders.DealerID) RIGHT JOIN ((Contracts INNER JOIN (SIN INNER JOIN Products
ON SIN.SINID = Products.SINID) ON Contracts.ContractID =
Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID =
[Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID
ORDER BY Orders.ShipDate;

I discovered something else - I imported the Products table from the
previous database with RI. This will not work. In searching for the
difference, I discovered that the products table that I am using that will
provide the report info (with RI on Contracts and SIN), when previewed, DOES
NOT display the ProductID; however, the imported table does. Why? I see no
difference in the properties at the bottom of the table form.

Sorry for so much confusing info.