access database engine cannot find a record - Query or join wrong?

[Shortskimper]

I am re-uploading V1 of the DB with the proper left join and dataentry to Off so that way it acts the way my screenshots show.

 

[ebs17]

I checked the database:

=> In order to understand any connections, my first look goes into the relationship window. Here the view went into emptiness, which in itself is a reason to close and bury the whole thing.

=> I had overcome myself for a moment and still had a quick look at queries and tables, which is exhausting. Tables have lookup fields. This is already problematic in itself, and the reasons for this have been extensively presented in many places.
But there are even lookup fields in tables that refer to tables that do not exist in this DB.

With that, I was finally allowed to break off my attempt to understand. The move to forms would be in vain without understanding the data.

 

[Gasman]

I did not even see that form that is in the above pictures?

 

[Shortskimper]

I checked the database:

=> In order to understand any connections, my first look goes into the relationship window. Here the view went into emptiness, which in itself is a reason to close and bury the whole thing.

=> I had overcome myself for a moment and still had a quick look at queries and tables, which is exhausting. Tables have lookup fields. This is already problematic in itself, and the reasons for this have been extensively presented in many places.
But there are even lookup fields in tables that refer to tables that do not exist in this DB.

With that, I was finally allowed to break off my attempt to understand. The move to forms would be in vain without understanding the data.

"But there are even lookup fields in tables that refer to tables that do not exist in this DB."
I was told to gut everything as I had to shrink the DB small enough to upload, so I did. This is the reason you are seeing that. I removed everything that did not pertain to this issue. It must have removed all relationships. I didn't see that. I will re-establish and upload.

"Tables have lookup fields."
Table lookups are currently being addressed at the moment as soon as Pat flagged me on it.

The form name is frmOrders. I have double checked that it is there. The problem is in the query as it will not allow more than one record to be created. I am less concerned about the form than I am the query. Simplifying and just running the query shows the query does not have the ability to add more than one record when a job and release are picked.

It's very simple.

Pick a job, pick a release number. Run qryOrders. Attempt to put more than one record "via the field orderbody" in qryOrders.

EDIT: I discovered why the relationships were removed when shrinking the DB for upload. I copy / pasted everything in to a new access DB to make sure it was clean and it did not take the relationships with it. Apologies on that.

 

[ebs17]

Attempt to put more than one record in qryOrders

Here is the query on purpose, the form references have been replaced with realistic ID's => this makes the form uninteresting.

SELECT
   tblJobNo.JobID,
   tblReleaseNum.ReleaseID,
   tblReleaseNum.JobNumID,
   tblOrders.OrderID,
   tblOrders.ReleaseNumID,
   tblOrders.OrderBody
FROM
   (tblJobNo
      INNER JOIN tblReleaseNum
      ON tblJobNo.JobID = tblReleaseNum.JobNumID
   )
   LEFT JOIN tblOrders
   ON tblReleaseNum.ReleaseID = tblOrders.ReleaseNumID
WHERE
   tblJobNo.JobID = 2737
      AND
   tblReleaseNum.ReleaseID = 4965

A query with several JOINs is not an Excel table (where you can do EVERYTHING) and in my view cannot be updated or expanded by data records. In exceptional cases, this can be successful with a first data set, but you can't work like this in the long run.

It is safe to practice if you make edits or new additions in exactly one table and this in a separate form. The query then only summarizes the data from the tables including changes.

 

[Shortskimper]

Here is the query on purpose, the form references have been replaced with realistic ID's => this makes the form uninteresting.

SELECT
   tblJobNo.JobID,
   tblReleaseNum.ReleaseID,
   tblReleaseNum.JobNumID,
   tblOrders.OrderID,
   tblOrders.ReleaseNumID,
   tblOrders.OrderBody
FROM
   (tblJobNo
      INNER JOIN tblReleaseNum
      ON tblJobNo.JobID = tblReleaseNum.JobNumID
   )
   LEFT JOIN tblOrders
   ON tblReleaseNum.ReleaseID = tblOrders.ReleaseNumID
WHERE
   tblJobNo.JobID = 2737
      AND
   tblReleaseNum.ReleaseID = 4965

A query with several JOINs is not an Excel table (where you can do EVERYTHING) and in my view cannot be updated or expanded by data records. In exceptional cases, this can be successful with a first data set, but you can't work like this in the long run.

It is safe to practice if you make edits or new additions in exactly one table and this in a separate form. The query then only summarizes the data from the tables including changes.

As i read. I'm having an Ephinay. I don't need the tblJobNo in this query at all. If i only had the left join from tblReleaseNum to tblOrders. Would this allow me to safely add records to tblOrders with the proper ReleaseID? I will test this. More important, is it safe?

 

[The_Doc_Man]

Our member ebs17 has pointed out the same thing I did (related to purity of purpose), but in different words. If you confine your actions to a single table, you should have no trouble augmenting or updating that table. If you try to do too much at once, you invite confusion.

The issue about why you would only get/return one record if you omit the details is because of what all SQL engines really do. They form combinations. They are combinatorial engines. If you have unique-match relationships (i.e. JOIN ... ON T1.X = T2.Y) and any member of the relationship is a prime key, you cannot get a list from that. ONLY if you have tables that are many-to-one will you get multiple combinations. The relationships you were using didn't seem to support that many-to-one - but a detail table where you can have many details dependent on some other field - THAT is how you get back multiple records.

 

[ebs17]

I will test this.

Good idea. In all variants! Own experiences and pain improve the process of understanding.

When manipulating the data in a table, you only have to observe its regulations (uniqueness, validity rules), and of course always the set referential integrity. That's clear, so I look for the relationships and the table definitions first. Those are the basics.

 

[Shortskimper]

I'm sorry. That came across wrong. Many people do feel they can ask us to build things for them as if we were their employee. You did not do that. But, you did fall into the typical. I did this. It doesn't work. How do I fix it? Which we can sometimes work with but not always. I did figure out what your original problem was and I described the solution. Please review #16. Did that not fix the initial problem? You then asked why you can not add multiple rows to the form and the answer is because it is linked to a specific, single record. So I posted an example of a m-m relationship in #18. Did you examine that? Do you understand the concept of a m-m relationship?

You CANNOT add multiple records to the order because you are adding a row to the order table and the order table has a foreign key that points to ONE record. That's where I mentioned that you might need to bind the order form to the details table rather than ReleaseNum.

The typical Order entry process is a many-many relationship at its lowest level.
tblOrder is the order header and has a FK to the customer
tblOrderDetails is the table that holds the items being ordered. Items NEVER go in the order header table which is what you seem to be doing. tblOrderDetails has two foreign keys. One to its parent table - tblOrder and a second to tblProduct which is the item being ordered. tblOrderDetails is the junction table that connects an order with many items. An order can contain many products and a product can be on many orders = many-many.

You don't have a many-many relationship that I can identify. I know the sample I linked to is not an order entry application so you have to put your thinking cap on to envision how the parts work to create an order. I know lots of people link to the Northwind Traders sample but that is garbage in my opinion. Yes it is sort of an order entry but its schema is poor as are its naming standards and functionality. To translate the m-m sample, open the Employee form. Then think of employee as the order and classes as the items being ordered.

I read this Post for at least an hour. and did some experimenting. I HAVE FOUND A WAY! Between you, Doc, and ebs. I thank all of you for having the patience and saying the same thing to me 12 different ways until I was able to fully grasp / understand it!

What I ended up doing -

Locking recordsource for frmOrders to qryOrders
Removing the Jobs table from qryOrders (KISS Method Doc!) as well as using the left join to tblOrders (Credit to you here!).
Creating a subform (that Doc had mentioned as well as you at one point) that pointed directly at tblOrders.

This allows the main form to parse the data. And the subform to use that data (master/child) to properly add more than one record attached to the ReleaseID.

EBS - I was about to give up as I had been attempting to do this for almost 4 days now. Your post about continuing to try new things really did invigorate me to keep trying.

If anyone is interested. I will reload the DB with the form working as intended. Well, sort of. I didn't want a datasheet view. But i'll work on that! The point is, It works! I will look at those lookup fields and attempt to write queries instead of using them as it seems everyone is VERY against them. I don't fully understand why (truthfully ignorant on this and haven't researched it much, But I will).

Thank you all!