Excel 2007 List of Materials converted into a report by building

2

2GWLAN

I have an Excel 2007 spreadsheet that is a List of Materials. The first row
consists of headings followed by building numbers. This is a bad start to
export to Access. Ultimately, I just have a onetime conversion to generate a
parts list by building. Doing this in Excel is slow to cut and paste as well
as removing the rows that do not have a part ordered. For this particular
job, there are 326 rows of various parts with the following headings-
CLIN Category Description PartNo Manufacturer UOMID

These six columns are followed by 31 building numbers with the number of
that part that needs to be purchased for a total of 37 columns. What is the
best way to pare this down into a report sorted by building number and
renaming the column that is currently headed by building number as Quantity?
For an example, Building 105 would have the six columns above, a seventh
column, Quantity; it would have only 57 rows of the parts that are ordered
for this building instead of all 326 possible parts.

I am a novice with Access, can easily import, and understand the basics of
queries and reports.
 
K

KARL DEWEY

uSE A UNION QUERY LIKE THIS ---
SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [Building 105] AS Quqnity
FROM [YourTableName]
UNION SELECT 115 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 115] AS Quqnity
FROM [YourTableName]
.............
UNION SELECT 245 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 245] AS Quqnity
FROM [YourTableName];
 
2

2GWLAN

Hi Karl,

I did the following SQL statement for all the buildings and when I hit RUN
it says Enter Parameter Value 105:

SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [Building 105] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 114 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 114] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 216 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 216] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 611 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 611] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 620 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 620] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 708 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 708] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 712 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 712] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1048 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1048] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1102 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1102] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1109 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1109] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1122 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1122] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1130 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1130] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1200 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1200] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1205 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1205] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1206 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1206] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1422 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1422] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1510 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1510] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1511 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1511] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1517 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1517] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1518 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1518] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1604 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1604] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1606 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1606] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1610 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1610] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1614 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1614] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1627 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1627] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1628 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1628] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1629 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1629] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1720 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1720] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1816 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1816] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1817 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1817] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1918 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1918] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]

I went to Query Design, Closed the Show Table Box, Union, then pasted the
above statement into SQL View Tab, RUN.

Just to restate, all the data imported from Excel went into one table not
multiple tables.
Where is my mistake?


Reading my first post, I previously stated without stating the question:
For an example, Building 105 would have the six columns above, a seventh
column, Quantity; it would have only 57 rows of the parts that are ordered
for this building instead of all 326 possible parts.


Here it is- How do I get a report not to list a row (for a part that is not
used) where the quantity field is blank and just list the 57 out of 326 rows
that contain an ordered part?


Thanks for your help again,

Phil Elmers

P.S. Your COUNTNUMBER statement last week for labels works great and will
save many man-hours!!!


KARL DEWEY said:
uSE A UNION QUERY LIKE THIS ---
SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [Building 105] AS Quqnity
FROM [YourTableName]
UNION SELECT 115 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 115] AS Quqnity
FROM [YourTableName]
............
UNION SELECT 245 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 245] AS Quqnity
FROM [YourTableName];

--
KARL DEWEY
Build a little - Test a little


2GWLAN said:
I have an Excel 2007 spreadsheet that is a List of Materials. The first row
consists of headings followed by building numbers. This is a bad start to
export to Access. Ultimately, I just have a onetime conversion to generate a
parts list by building. Doing this in Excel is slow to cut and paste as well
as removing the rows that do not have a part ordered. For this particular
job, there are 326 rows of various parts with the following headings-
CLIN Category Description PartNo Manufacturer UOMID

These six columns are followed by 31 building numbers with the number of
that part that needs to be purchased for a total of 37 columns. What is the
best way to pare this down into a report sorted by building number and
renaming the column that is currently headed by building number as Quantity?
For an example, Building 105 would have the six columns above, a seventh
column, Quantity; it would have only 57 rows of the parts that are ordered
for this building instead of all 326 possible parts.

I am a novice with Access, can easily import, and understand the basics of
queries and reports.
Click to expand...
Click to expand...
 
2

2GWLAN

Good Morning Karl,

Please disregard most of the previous post dated 6/17/2008 5:24 PM PST ...
I found the syntax error and got the query to run. BUT...

How do I get a separate report for each building (105, 115, 216, etc... 31
in all) and remove all the rows (for a part that is 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 by each building.


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.

Thanks,

Phil Elmers








2GWLAN said:
Hi Karl,

I did the following SQL statement for all the buildings and when I hit RUN
it says Enter Parameter Value 105:

SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [Building 105] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 114 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 114] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 216 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 216] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 611 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 611] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 620 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 620] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 708 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 708] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 712 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 712] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1048 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1048] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1102 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1102] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1109 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1109] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1122 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1122] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1130 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1130] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1200 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1200] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1205 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1205] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1206 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1206] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1422 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1422] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1510 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1510] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1511 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1511] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1517 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1517] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1518 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1518] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1604 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1604] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1606 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1606] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1610 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1610] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1614 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1614] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1627 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1627] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1628 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1628] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1629 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1629] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1720 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1720] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1816 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1816] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1817 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1817] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]
UNION SELECT 1918 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 1918] AS Quantity
FROM [Shaw LOM- Unrestricted- v1]

I went to Query Design, Closed the Show Table Box, Union, then pasted the
above statement into SQL View Tab, RUN.

Just to restate, all the data imported from Excel went into one table not
multiple tables.
Where is my mistake?


Reading my first post, I previously stated without stating the question:
For an example, Building 105 would have the six columns above, a seventh
column, Quantity; it would have only 57 rows of the parts that are ordered
for this building instead of all 326 possible parts.


Here it is- How do I get a report not to list a row (for a part that is not
used) where the quantity field is blank and just list the 57 out of 326 rows
that contain an ordered part?


Thanks for your help again,

Phil Elmers

P.S. Your COUNTNUMBER statement last week for labels works great and will
save many man-hours!!!


KARL DEWEY said:
uSE A UNION QUERY LIKE THIS ---
SELECT 105 AS Building, CLIN, Category, Description, PartNo, Manufacturer,
UOMID, [Building 105] AS Quqnity
FROM [YourTableName]
UNION SELECT 115 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 115] AS Quqnity
FROM [YourTableName]
............
UNION SELECT 245 AS Building, CLIN, Category, Description, PartNo,
Manufacturer, UOMID, [Building 245] AS Quqnity
FROM [YourTableName];

--
KARL DEWEY
Build a little - Test a little


2GWLAN said:
I have an Excel 2007 spreadsheet that is a List of Materials. The first row
consists of headings followed by building numbers. This is a bad start to
export to Access. Ultimately, I just have a onetime conversion to generate a
parts list by building. Doing this in Excel is slow to cut and paste as well
as removing the rows that do not have a part ordered. For this particular
job, there are 326 rows of various parts with the following headings-
CLIN Category Description PartNo Manufacturer UOMID

These six columns are followed by 31 building numbers with the number of
that part that needs to be purchased for a total of 37 columns. What is the
best way to pare this down into a report sorted by building number and
renaming the column that is currently headed by building number as Quantity?
For an example, Building 105 would have the six columns above, a seventh
column, Quantity; it would have only 57 rows of the parts that are ordered
for this building instead of all 326 possible parts.

I am a novice with Access, can easily import, and understand the basics of
queries and reports.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Records with blank fields and creating a report by building 3
Parts List/Bill of Materials 2
Hitting the limits with Excel and need about 1 million rows of data.What is the best option for what 1
How to add a non-bold Page X of Y building block in Bottom of Page 4
Trying to create a list of prices from a web page 2
viewing a subset of information from a large spreadsheet 2
Excel Query setup 0
bug in excel 2007 for sorting function? 3

Top