Remove Records with blank fields and creating a report by building

[2GWLAN]

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

 

[Klatuu]

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.

 

[2GWLAN]

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

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

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

 

[KARL DEWEY]

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

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

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

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