|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
April 20th, 2005, 01:52 AM
|
|||
|
|||
How many characters do you have in your expression in your query column?
Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news  I have a field from a query where I have concatenated multiple fields to show (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com 
|
|
#12
April 20th, 2005, 03:27 AM
|
|||
|
|||
|
The expressions are huge.
The report was working perfectly until the boss decided he wanted to see all the current inventory in house for the build of each job on the same report with the customer requirements. The original report takes a lot of info and makes a table. Perhaps I'll need to somehow include the build's inventory at each operation into the make table query instead of trying to include it later. But, I'm afraid I'll still have the same problem of too many characters to show on the report. Here are the current expression. I have divided them into two sub queries. The first:qryBOMA contains the following: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] & " ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " & [8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & " ** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p], qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q], qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p], qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q], qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p], qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p], qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q] FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb = qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON (qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN = qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND (qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb = qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON (qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN = qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND (qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb = qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON (qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN = qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND (qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb = qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON (qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN); The second is qryBOMB and contains: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " & [18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p] & " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb = qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON (qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN = qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND (qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb = qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON (qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN = qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND (qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb = qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON (qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN); The third query combines the first two into qryBOM as follows: SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND (qryBOMA.FPN = qryBOMB.FPN); This is then joined to the query that the report pulls from as follows: SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO, RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb, RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate, RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum, RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2, RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2, RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue, IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4, RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4, RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty], RelReportPastDue.StandardPack, qryBOM.Inv FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON (RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND (RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND (RelReportPastDue.PN = qryBOM.FPN); "Duane Hookom" wrote: How many characters do you have in your expression in your query column? Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news I have a field from a query where I have concatenated multiple fields to show (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
|
|
#13
April 20th, 2005, 06:53 AM
|
|||
|
|||
|
I thought you stated all the characters were displaying in the query. Try
remove the "DISTINCT" from the final query. Your table seems highly un-normalized. I can't help but think there has to be a better way but I hate to even ask deeper questions. -- Duane Hookom MS Access MVP "neenmarie" wrote in message news The expressions are huge. The report was working perfectly until the boss decided he wanted to see all the current inventory in house for the build of each job on the same report with the customer requirements. The original report takes a lot of info and makes a table. Perhaps I'll need to somehow include the build's inventory at each operation into the make table query instead of trying to include it later. But, I'm afraid I'll still have the same problem of too many characters to show on the report. Here are the current expression. I have divided them into two sub queries. The first:qryBOMA contains the following: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] & " ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " & [8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & " ** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p], qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q], qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p], qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q], qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p], qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p], qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q] FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb = qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON (qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN = qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND (qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb = qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON (qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN = qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND (qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb = qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON (qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN = qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND (qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb = qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON (qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN); The second is qryBOMB and contains: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " & [18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p] & " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb = qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON (qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN = qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND (qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb = qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON (qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN = qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND (qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb = qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON (qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN); The third query combines the first two into qryBOM as follows: SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND (qryBOMA.FPN = qryBOMB.FPN); This is then joined to the query that the report pulls from as follows: SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO, RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb, RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate, RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum, RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2, RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2, RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue, IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4, RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4, RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty], RelReportPastDue.StandardPack, qryBOM.Inv FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON (RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND (RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND (RelReportPastDue.PN = qryBOM.FPN); "Duane Hookom" wrote: How many characters do you have in your expression in your query column? Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news I have a field from a query where I have concatenated multiple fields to show (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
|
|
#14
April 20th, 2005, 01:39 PM
|
|||
|
|||
|
Thank you for trying. Removing 'Distinct' does allow all the characters to
appear, but adds extra records to the query and report. What do you mean by 'un-normalized'. "Duane Hookom" wrote: I thought you stated all the characters were displaying in the query. Try remove the "DISTINCT" from the final query. Your table seems highly un-normalized. I can't help but think there has to be a better way but I hate to even ask deeper questions. -- Duane Hookom MS Access MVP "neenmarie" wrote in message news The expressions are huge. The report was working perfectly until the boss decided he wanted to see all the current inventory in house for the build of each job on the same report with the customer requirements. The original report takes a lot of info and makes a table. Perhaps I'll need to somehow include the build's inventory at each operation into the make table query instead of trying to include it later. But, I'm afraid I'll still have the same problem of too many characters to show on the report. Here are the current expression. I have divided them into two sub queries. The first:qryBOMA contains the following: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] & " ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " & [8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & " ** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p], qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q], qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p], qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q], qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p], qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p], qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q] FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb = qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON (qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN = qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND (qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb = qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON (qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN = qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND (qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb = qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON (qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN = qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND (qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb = qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON (qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN); The second is qryBOMB and contains: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " & [18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p] & " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb = qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON (qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN = qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND (qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb = qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON (qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN = qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND (qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb = qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON (qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN); The third query combines the first two into qryBOM as follows: SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND (qryBOMA.FPN = qryBOMB.FPN); This is then joined to the query that the report pulls from as follows: SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO, RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb, RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate, RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum, RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2, RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2, RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue, IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4, RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4, RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty], RelReportPastDue.StandardPack, qryBOM.Inv FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON (RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND (RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND (RelReportPastDue.PN = qryBOM.FPN); "Duane Hookom" wrote: How many characters do you have in your expression in your query column? Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news
I have a field from a query where I have concatenated multiple fields toshow (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
|
|
#15
April 20th, 2005, 06:40 PM
|
|||
|
|||
|
I couldn't begin to figure out how to remove duplicates from your queries.
If you think you need all of the text, then you will have change your solution. To find out about normalization, check these links http://www.ltcomputerdesigns.com/JCR...abaseDesign101. I don't know the origin of your data but seeing repeating objects suggests there has to be a better solution. -- Duane Hookom MS Access MVP "neenmarie" wrote in message ... Thank you for trying. Removing 'Distinct' does allow all the characters to appear, but adds extra records to the query and report. What do you mean by 'un-normalized'. "Duane Hookom" wrote: I thought you stated all the characters were displaying in the query. Try remove the "DISTINCT" from the final query. Your table seems highly un-normalized. I can't help but think there has to be a better way but I hate to even ask deeper questions. -- Duane Hookom MS Access MVP "neenmarie" wrote in message news The expressions are huge. The report was working perfectly until the boss decided he wanted to see all the current inventory in house for the build of each job on the same report with the customer requirements. The original report takes a lot of info and makes a table. Perhaps I'll need to somehow include the build's inventory at each operation into the make table query instead of trying to include it later. But, I'm afraid I'll still have the same problem of too many characters to show on the report. Here are the current expression. I have divided them into two sub queries. The first:qryBOMA contains the following: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] & " ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " & [8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & " ** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p], qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q], qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p], qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q], qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p], qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p], qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q] FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb = qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON (qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN = qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND (qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb = qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON (qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN = qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND (qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb = qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON (qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN = qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND (qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb = qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON (qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN); The second is qryBOMB and contains: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " & [18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p] & " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb = qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON (qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN = qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND (qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb = qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON (qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN = qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND (qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb = qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON (qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN); The third query combines the first two into qryBOM as follows: SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND (qryBOMA.FPN = qryBOMB.FPN); This is then joined to the query that the report pulls from as follows: SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO, RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb, RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate, RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum, RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2, RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2, RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue, IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4, RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4, RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty], RelReportPastDue.StandardPack, qryBOM.Inv FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON (RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND (RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND (RelReportPastDue.PN = qryBOM.FPN); "Duane Hookom" wrote: How many characters do you have in your expression in your query column? Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news
I have a field from a query where I have concatenated multiple fieldsto show (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
|
|
#16
April 20th, 2005, 07:10 PM
|
|||
|
|||
|
Thank your for all the time you spent trying to help me. I guess I'll
attempt to find another approach. "Duane Hookom" wrote: I couldn't begin to figure out how to remove duplicates from your queries. If you think you need all of the text, then you will have change your solution. To find out about normalization, check these links http://www.ltcomputerdesigns.com/JCR...abaseDesign101. I don't know the origin of your data but seeing repeating objects suggests there has to be a better solution. -- Duane Hookom MS Access MVP "neenmarie" wrote in message ... Thank you for trying. Removing 'Distinct' does allow all the characters to appear, but adds extra records to the query and report. What do you mean by 'un-normalized'. "Duane Hookom" wrote: I thought you stated all the characters were displaying in the query. Try remove the "DISTINCT" from the final query. Your table seems highly un-normalized. I can't help but think there has to be a better way but I hate to even ask deeper questions. -- Duane Hookom MS Access MVP "neenmarie" wrote in message news
The expressions are huge.The report was working perfectly until the boss decided he wanted to see all the current inventory in house for the build of each job on the same report with the customer requirements. The original report takes a lot of info and makes a table. Perhaps I'll need to somehow include the build's inventory at each operation into the make table query instead of trying to include it later. But, I'm afraid I'll still have the same problem of too many characters to show on the report. Here are the current expression. I have divided them into two sub queries. The first:qryBOMA contains the following: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] & " ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " & [8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & " ** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p], qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q], qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p], qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q], qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p], qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p], qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q] FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb = qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON (qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN = qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND (qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb = qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON (qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN = qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND (qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb = qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON (qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN = qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND (qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb = qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON (qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN); The second is qryBOMB and contains: SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " & [18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p] & " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb = qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON (qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN = qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND (qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb = qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON (qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN = qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND (qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb = qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON (qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN); The third query combines the first two into qryBOM as follows: SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND (qryBOMA.FPN = qryBOMB.FPN); This is then joined to the query that the report pulls from as follows: SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO, RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb, RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate, RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum, RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2, RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2, RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue, IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4, RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4, RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty], RelReportPastDue.StandardPack, qryBOM.Inv FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON (RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND (RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND (RelReportPastDue.PN = qryBOM.FPN); "Duane Hookom" wrote: How many characters do you have in your expression in your query column? Your structure looks to be a bit un-normalized. Can you provide the complete SQL view of your query? -- Duane Hookom MS Access MVP "neenmarie" wrote in message news
I have a field from a query where I have concatenated multiple fieldsto show (Part Numbers, Operations and Inventory amounts on hand) for all components of a finished part number where I'm using ** as a spacer between each set. ([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up to 25 sets). This concatenated field is attached in a query by a job and finished part number. I've done this to be able to include this info as a single line in a very complicated report. The report has no groupings, but is sorted by Job Number and Part Number. All the info I'm seeking is showing up in the query, but is being truncated on the report. Any idea what I can do to show all the data from the query field on the report? "Rick Brandt" wrote: neenmarie wrote: This is a field in a query to combines both text and number fields. How can I change the combined field to a memo type? Truncation should not be a problem in the scenario you are describing. What is the query expression? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Linking text boxes | Volunteer Mom | Publisher | 7 | November 12th, 2008 01:29 AM |
| Combo Box & Text Box | AccessRookie | Using Forms | 3 | April 6th, 2005 11:33 PM |
| Error: Microsoft jet database engine does not recognize " as a va | HSL | Setting Up & Running Reports | 9 | April 5th, 2005 06:57 PM |
| Access reports with a horizontal line after each record??? | Bill via AccessMonster.com | Setting Up & Running Reports | 6 | March 9th, 2005 04:51 PM |
| Dynamic Heading in cross tab qry report | ECA | Setting Up & Running Reports | 3 | December 13th, 2004 11:09 PM |
Linear Mode
