Sub Query woahs

[ChrisHannah]

Hello

I have a sub form that lists the particular purchases made by that client on that clients page. This does not work (except for one result)

i have a form that i select my sales lead this inputs into a customer query that filters the results by sales lead this outputs to a form this form then has a sub form that shows the sales (a breif summary of the sales) this breif summary of sales does not work.

I receive the error message "this expression is typed incorrectly, or it is too complex to be evaluated"

This sub-form does work if i do not link master and child forms as a filter (but this just shows a list of all sales for all users which is not helpful)

i'm not sure what information you need to help me:
The form has a dropdown for sales lead this links to the query for that customer
The values used in that link to the subform, if i link customer ID to customer ID (i have varified these are the same in both query results) this gives me the above error.

i have spent literally hours messing with the formatting and i cannot get it to work as well as re-creating both queries and forms with no positive results.

 

[Ranman256]

The master child forms will work by setting the child form properties:
Link parent field,
Link child field.
Both must be set to the parent key field. (ClientID)

Once these are set, the child form will only show records belonging to the parent client.
When you change records,so does the child form.
When you filter the parent,so does the child form.

 

[sneuberg]

If the record source of the subform is a query that has a join that could cause the symptoms you describe. It's better to stick to one table per form.

 

[ChrisHannah]

hello Thanks for the reply, i have linked the child and master and this is what causes the issue that i have (i have linked these child and masters on several other forms so i know how to do it i just do not know why this is going wrong. i had this working previously but had to set a secondary level of complexity so i believe this other level of complexity is what is causing the issue. (this is based on a union ALL query)

 

[ChrisHannah]

If the record source of the subform is a query that has a join that could cause the symptoms you describe. It's better to stick to one table per form.

is there a way around this, i have a rather complex query because i need to take values from multiple comma seperated inputs and compare that to a list and return a set of values

 

[plog]

this is based on a union ALL query

You just passed my threshold for questioning your table structure. Complex queries, low post count, now you mention a UNION query--Can you post a screenshot of your relationships?

 

[sneuberg]

is there a way around this, i have a rather complex query because i need to take values from multiple comma seperated inputs and compare that to a list and return a set of values

Maybe. Could you upload your database or a stripped down version so that we can see how complex this is.

 

[sneuberg]

You just passed my threshold for questioning your table structure. Complex queries, low post count, now you mention a UNION query--Can you post a screenshot of your relationships?

@ChrisHannah: Until you haves ten posts any images will have to be put in a zip file before uploading.

Edit: I guess I was wrong about this. I see a PNG has been upload.

 

[ChrisHannah]

You just passed my threshold for questioning your table structure. Complex queries, low post count, now you mention a UNION query--Can you post a screenshot of your relationships?

Hello

I have Three Tables related to this output

one is customer this is my customer list this is not related directly to the others but there are unique relatable values between these I use an input form to add sales reference numbers to a customer these are kept on a separate table
SalesForceEngagements this relates to my SalesForceData by sales number this is unique.

I have a query that splits up these values
GMD-100118-12079,GMD-100118-124991,GMD-100118-12112,GMD-100118-12429,GMD-100118-12382,
into single entries in a column.

SELECT AccountData.[Customer ID], SalesForceEngagements.ID AS [Engagement ID], SalesForceEngagements.[Salesforce Refeference] AS [Sales Force Reference(s)], Left([Sales Force Reference(s)],InStr([Sales Force Reference(s)],",")-1) AS SF1, Mid([Sales Force Reference(s)],InStr([Sales Force Reference(s)],",")+1) AS SF1a, Left([SF1a],InStr([SF1a],",")-1) AS SF2, Mid([SF1a],InStr([SF1a],",")+1) AS SF1b, Left([SF1b],InStr([SF1b],",")-1) AS SF3, Mid([SF1b],InStr([SF1b],",")+1) AS SF1c, Left([SF1c],InStr([SF1c],",")-1) AS SF4, Mid([SF1c],InStr([SF1c],",")+1) AS SF1d, Left([SF1d],InStr([SF1d],",")-1) AS SF5, Mid([SF1d],InStr([SF1d],",")+1) AS SF1e, Left([SF1e],InStr([SF1e],",")-1) AS SF6, Mid([SF1e],InStr([SF1e],",")+1) AS SF1f, Left([SF1f],InStr([SF1f],",")-1) AS SF7, Mid([SF1f],InStr([SF1f],",")+1) AS SF1g, Left([SF1g],InStr([SF1g],",")-1) AS SF8
FROM (SalesForceEngagements LEFT JOIN SalesForceData ON SalesForceEngagements.[Salesforce Refeference] = SalesForceData.[Opportunity Reference]) INNER JOIN AccountData ON SalesForceEngagements.[Customer Name] = AccountData.[Customer Name]
WHERE (((SalesForceEngagements.[Salesforce Refeference])<>""));

my union query is complex but essentially it takes this string of split up text from this query salesforce engagements
GMD-100118-12079 and adds some extra details like ££ from SalesForceData

SELECT SalesForceSubQuery0.[Customer ID], SalesForceData.[Account Name], SalesForceData.[Opportunity Name], SalesForceData.[Sales Stage], SalesForceData.TCV, SalesForceSubQuery0.SF1
FROM SalesForceSubQuery0 INNER JOIN SalesForceData ON SalesForceSubQuery0.SF1 = SalesForceData.[Opportunity Reference]
UNION ALL
SELECT SalesForceSubQuery0.[Customer ID], SalesForceData.[Account Name], SalesForceData.[Opportunity Name], SalesForceData.[Sales Stage], SalesForceData.TCV, SalesForceSubQuery0.SF2
FROM SalesForceSubQuery0 INNER JOIN SalesForceData ON SalesForceSubQuery0.SF2 = SalesForceData.[Opportunity Reference]
UNION ALL
SELECT SalesForceSubQuery0.[Customer ID], SalesForceData.[Account Name], SalesForceData.[Opportunity Name], SalesForceData.[Sales Stage], SalesForceData.TCV, SalesForceSubQuery0.SF3
FROM SalesForceSubQuery0 INNER JOIN SalesForceData ON SalesForceSubQuery0.SF3 = SalesForceData.[Opportunity Reference];