Junction table query textbox (1 Viewer)

[UltimateNeo]

Hello I have a report and made a textbox with the control source =dsum("QTY","[JobsT]", "InspectionID = 1") so QTY is in the JobsT and in a junction table i have InspectionID that is linked by jobno. If i set the report recordsource with the fields it returns the result. When i use the formula it does not work. I have also tried =dsum("QTY","ReportName", "InspectionID = 1") still to an error it cant find the InspectionID field. Any ideas would be greatly appreciated.

 

[mike60smart]

Hello I have a report and made a textbox with the control source =dsum("QTY","[JobsT]", "InspectionID = 1") so QTY is in the JobsT and in a junction table i have InspectionID that is linked by jobno. If i set the report recordsource with the fields it returns the result. When i use the formula it does not work. I have also tried =dsum("QTY","ReportName", "InspectionID = 1") still to an error it cant find the InspectionID field. Any ideas would be greatly appreciated.

Can you upload your database?

 

[UltimateNeo]

This is a guide to what I have done, i have not got my database from work here. As it is hard to get it back into work. Hope this helps

 

[mike60smart]

This is a guide to what I have done, i have not got my database from work here. As it is hard to get it back into work. Hope this helps

Hi

See the amended Report

I added a field to the JobT called Stage.

Then amended your DSum to read as follows:-

=DSum("QTY","[JobsT]","[Stage] =1")

 

[Gasman]

The inspectionID needs to be in the same domain.
Create query that brings in both, and dlookup that.

 

[Pat Hartman]

Access is always trying to help us but sometimes, it isn't very helpful. This is one of those cases. This "feature" used to apply only to reports but some newbee on the Access team thought it would be a fabulous idea to add the "feature" to forms also so you need to watch out there also. what is the "feature"? Simple, if you don't bind a column of the recordSource query/table to a control on the form/report, Access, "helpfully" removes the column from the query. Oh, it leaves it there where you can see it but internally, it is gone. You can fix the problem by binding InspectionTypeID to a control on the form. You can make the control tiny and hidden. It just needs to be there if you expect to reference it.

Now for the other issues. Hopefully your real database doesn't have the issues I will point out next.
1. Naming all the PKs "ID" is silly and makes it much more difficult for others to figure out what the relationships are without seeing them in the diagram.
2. you never drew a relationship between JobsT and JobInspectionT so I wasn't sure whether it was a naming issue or a bad relationship.
3. Turns out to be a bad relationship. Relationships ( and 99% of joins) are PK to FK and not data field to data field. Your join in the query bound to the report is data field to data field -- this is 100% wrong for permanent tables. The only time it makes sense is if you are doing clean up and you need to join two queries or tables on non-key fields to find duplicates. For example to match two files of addresses, you try to match on names but you should also match on address1 and phone number and any other fields that you think will highlight duplicates.

 

[UltimateNeo]

Hi

See the amended Report

I added a field to the JobT called Stage.

Then amended your DSum to read as follows:-

=DSum("QTY","[JobsT]","[Stage] =1")

I am not sure that would work. As say the job comes in 2 x for a stage inspection, then it comes in for it's final inspection. I am just wanting to count the qty to give a total of stage inspections and a total for final inspections.