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 |
#1
|
|||
|
|||
Remove Records with blank fields and creating a report by building
Previously I created (with the Help of Karl Dewey) a Query to solve a table
problem. How do I get a separate report for each building (105, 115, 216, etc... 31 in all) and remove all the rows (for parts that are not used) where the quantity field is blank (does not have a 0)? Thus, I only want to list the 57 out of 326 possible rows for each building (deleting the blank quantity rows) that contain an ordered part for 105, 115, 216, etc...? In other words, a parts list for each building of only the parts required and not all parts available. Here are my column headings for the Query called [Query By Bldg Number]; Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, Quantity I now have a huge query, 10044 records many of which the quantity is blank because that part was not required for that building. Each building has 326 records with many that do not require that part. Time to streamline. NOTE: The previous Query Conversion was under General Questions- Subject: Excel 2007 List of Materials converted into a report by building Posted: 6/17/2008 11:11 AM PST Thanks, Phil Elmers |
#2
|
|||
|
|||
Remove Records with blank fields and creating a report by building
To remove rows with a 0 Quantity, add a Where clause to your query:
WHERE Quantity 0 You can either run an individual report for each building and filter it's recordset on Building or you can use a Group Header for Building and have it force a new page before. -- Dave Hargis, Microsoft Access MVP "2GWLAN" wrote: Previously I created (with the Help of Karl Dewey) a Query to solve a table problem. How do I get a separate report for each building (105, 115, 216, etc... 31 in all) and remove all the rows (for parts that are not used) where the quantity field is blank (does not have a 0)? Thus, I only want to list the 57 out of 326 possible rows for each building (deleting the blank quantity rows) that contain an ordered part for 105, 115, 216, etc...? In other words, a parts list for each building of only the parts required and not all parts available. Here are my column headings for the Query called [Query By Bldg Number]; Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, Quantity I now have a huge query, 10044 records many of which the quantity is blank because that part was not required for that building. Each building has 326 records with many that do not require that part. Time to streamline. NOTE: The previous Query Conversion was under General Questions- Subject: Excel 2007 List of Materials converted into a report by building Posted: 6/17/2008 11:11 AM PST Thanks, Phil Elmers |
#3
|
|||
|
|||
Remove Records with blank fields and creating a report by buil
Still need help,
I have tried to figure out how to do where clause to no avail. Here is an extract from the query for the first 4 buildings out of 31 total. SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [105] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 114 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [114] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 216 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [216] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 611 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [611] AS Quantity How do I modify this query with the Where statement? Is “WHERE Quantity 0” in the quantity column the same as a blank field or is something else required such as NULL? IF this query can’t be modified, how do I format a new query with the column and table/query info from my first post? Also, in the WHERE statement; A space between where and Quantity, but is a space required on each side of ? Is the syntax for the column heading “Quantity” need to be in brackets or not? Sorry, I have a lot to learn… For the 2nd part, how do I run a report based on each building number from the Query called [Query By Bldg Number] to separate each building in a report with page breaks. I would prefer not to run 31 reports total based on each building. I think your second part, “use a Group Header for Building and have it force a new page before” seems cleaner, but I have no idea how to do it. Thanks, Phil Elmers "Klatuu" wrote: To remove rows with a 0 Quantity, add a Where clause to your query: WHERE Quantity 0 You can either run an individual report for each building and filter it's recordset on Building or you can use a Group Header for Building and have it force a new page before. -- Dave Hargis, Microsoft Access MVP "2GWLAN" wrote: Previously I created (with the Help of Karl Dewey) a Query to solve a table problem. How do I get a separate report for each building (105, 115, 216, etc... 31 in all) and remove all the rows (for parts that are not used) where the quantity field is blank (does not have a 0)? Thus, I only want to list the 57 out of 326 possible rows for each building (deleting the blank quantity rows) that contain an ordered part for 105, 115, 216, etc...? In other words, a parts list for each building of only the parts required and not all parts available. Here are my column headings for the Query called [Query By Bldg Number]; Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, Quantity I now have a huge query, 10044 records many of which the quantity is blank because that part was not required for that building. Each building has 326 records with many that do not require that part. Time to streamline. NOTE: The previous Query Conversion was under General Questions- Subject: Excel 2007 List of Materials converted into a report by building Posted: 6/17/2008 11:11 AM PST Thanks, Phil Elmers |
#4
|
|||
|
|||
Remove Records with blank fields and creating a report by buil
SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [105] AS Quantity FROM [Shaw LOM- Unrestricted- v1] WHERE [105] 0 UNION SELECT 114 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [114] AS Quantity FROM [Shaw LOM- Unrestricted- v1] WHERE [114] 0 UNION SELECT 216 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [216] AS Quantity FROM [Shaw LOM- Unrestricted- v1] WHERE [216] 0 UNION SELECT 611 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [611] AS Quantity WHERE [611] 0 -- KARL DEWEY Build a little - Test a little "2GWLAN" wrote: Still need help, I have tried to figure out how to do where clause to no avail. Here is an extract from the query for the first 4 buildings out of 31 total. SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [105] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 114 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [114] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 216 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [216] AS Quantity FROM [Shaw LOM- Unrestricted- v1] UNION SELECT 611 AS Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, [611] AS Quantity How do I modify this query with the Where statement? Is “WHERE Quantity 0” in the quantity column the same as a blank field or is something else required such as NULL? IF this query can’t be modified, how do I format a new query with the column and table/query info from my first post? Also, in the WHERE statement; A space between where and Quantity, but is a space required on each side of ? Is the syntax for the column heading “Quantity” need to be in brackets or not? Sorry, I have a lot to learn… For the 2nd part, how do I run a report based on each building number from the Query called [Query By Bldg Number] to separate each building in a report with page breaks. I would prefer not to run 31 reports total based on each building. I think your second part, “use a Group Header for Building and have it force a new page before” seems cleaner, but I have no idea how to do it. Thanks, Phil Elmers "Klatuu" wrote: To remove rows with a 0 Quantity, add a Where clause to your query: WHERE Quantity 0 You can either run an individual report for each building and filter it's recordset on Building or you can use a Group Header for Building and have it force a new page before. -- Dave Hargis, Microsoft Access MVP "2GWLAN" wrote: Previously I created (with the Help of Karl Dewey) a Query to solve a table problem. How do I get a separate report for each building (105, 115, 216, etc... 31 in all) and remove all the rows (for parts that are not used) where the quantity field is blank (does not have a 0)? Thus, I only want to list the 57 out of 326 possible rows for each building (deleting the blank quantity rows) that contain an ordered part for 105, 115, 216, etc...? In other words, a parts list for each building of only the parts required and not all parts available. Here are my column headings for the Query called [Query By Bldg Number]; Building, CLIN, Category, Description, PartNo, Manufacturer, UOMID, Quantity I now have a huge query, 10044 records many of which the quantity is blank because that part was not required for that building. Each building has 326 records with many that do not require that part. Time to streamline. NOTE: The previous Query Conversion was under General Questions- Subject: Excel 2007 List of Materials converted into a report by building Posted: 6/17/2008 11:11 AM PST Thanks, Phil Elmers |
Thread Tools | |
Display Modes | |
|
|