Multi-table query is not including new data from tables

[jaaldo]

Hi all,



At work, we've recently created a database to easily access all the documents required for any given order/invoice/delivery etc, where a single order may be split over several deliveries and several orders can be invoiced together. I've created a multi-table query to bring up all the related forms when an invoice number/delivery number/sales order number is given, but it's stopped updating any new orders that are being entered.

I've tried looking online for answers, but I'm very much an Access novice, especially when it comes to anything dealing with SQL. I have seen that empty fields in a record can stop it, and some of the new records don't have every field included, largely because the original database had a much larger scope than what it's being used for. Is there anyway to get it updating again?

Here's the SQL code for the query that I have:

SELECT tblCustomers.CleanName, tblDeliveries.DeliveryNo, tblDeliveries.PickList, tblDeliveries.CoC, tblDeliveries.DeliveryNote, tblInvoice.Invoice, tblInvoice.InvoiceNo, tblSalesOrderAcks.SalesOrderAckNo, tblSalesOrderAcks.SalesOrderAck, tblSalesOrderAcks.PurchaseOrder, tblDeliveries.[Airway bill/Bill of Lading]FROM (((tblCustomers INNER JOIN tblSalesOrderAcks ON tblCustomers.[CustCode] = tblSalesOrderAcks.[CustAccount]) INNER JOIN tblSalesOrderLines ON tblSalesOrderAcks.[SalesOrderAckNo] = tblSalesOrderLines.[SalesOrderNo]) INNER JOIN (tblDeliveries INNER JOIN tblDeliveryLineNo ON tblDeliveries.[DeliveryNo] = tblDeliveryLineNo.[DeliveryNo]) ON tblSalesOrderLines.[SalesOrderLineNo] = tblDeliveryLineNo.[SalesOrderLineNo]) INNER JOIN (tblInvoice INNER JOIN tblInvoiceLineNo ON tblInvoice.[InvoiceNo] = tblInvoiceLineNo.[InvoiceNo]) ON tblDeliveryLineNo.[DeliveryLineNo] = tblInvoiceLineNo.[DeliveryLineNo];

Thanks

[ranman256]

dont update using multi table queries. try to update using a single table.
some queries may loose records depending on the join. (Inner vs outer)
same w updates.

[jaaldo]

Thanks for the quick response. I'm not updating with the query, I'm updating each table manually, but most of the new records I've created since I created the query don't show up in the query, only in the individual tables themselves

Thanks

[orange]

Please tell us specifically what this really means:

I'm updating each table manually

[jaaldo]

I'm updating each table either in the table itself, or in a form that is just a 1-to-1 match with the table. I'm not using the query itself to update the tables, just to fetch the relevant forms when necessary.

Thanks

[orange]

It is considered good practice to do data entry via Forms. That is, no direct interaction between users and tables.

Many new to Access/database tend to jump in to physical database. Here is a link to a data model from Barry Williams' site that deals with Products, Orders and Deliveries that may help with design. And this one dealing with Customers and Invoices. Many issues related to inability to access or update records stems from the underlying database (tables and relationships) structure.
I recommend creating a data model(pencil and paper) based on a description of the "business" you are trying to support with automation. You can test the model with "sample data" and "sample scenarios". If the model doesn't support your needs, you can adjust the model until it performs as needed. At that point you have a blueprint for your tables and relationships.
See the links in my signature for other info that may be helpful.

Good luck with your project.