Combing Fields (1 Viewer)

[Leo]

I am trying to create a report that will display all products sold for the month.

My Issue is that form uses fields listing; Product-1,Product-2..etc - as they appear in the table.

How do create a query that will combine the field Product-1,Product-2 under the a field that list just "Products".

 

[Pat Hartman]

If there really is a hierarchy built into the actual productID, you can use the grouping feature of the reportwriter to group by say the first 7 characters of productID. If that is not the case, you'll need to add a new column to the product table and assign each product to a group. You can then group on the new column.

 

[Leo]

I do not understand the "hierarchy" you mentioned or the grouping (which I beleive I can only use in a report).

I was looking for the answer in a query. But, are you saying that I can combine field directly in a report?

You you be able to simplify your answer?

Thanks again

 

[Pat Hartman]

If your product numbers are structured as:
123003
123128
123399
or something similar where there are some fixed number of leading characters, you can use the report's grouping feature. But if the numbers you want to group have no fixed set of leading characters, you will need to actually add a new column to the table so you can enter the specific groups. For example, the following three product numbers do not have any characters in common. If you want them to be grouped together, you will need a field that is common to the three records to accomplish the grouping.
178986
A24487
189222

Grouping in a query implies summarization. Is that what you want to do? A query cannot return both detail and grouped records as you would see on a report with subtotals. It only returns a recordset containing one or the other.

 

[Leo]

Let me attempt to simplify my question even more. I thing I am not using the correct terms.

What I have is a form that looks like:
Qty Product Price Serial #
1 AA 102 Product $1,234.34 4374-1
4 DD 123 Product $1,133.22 5612-1
3 AM/FM Radio $0

In my example, “Product” is actually three fields (Product-1, Product-2 & Product-3) in my Customers Table. I also have a Pricing table where I am retrieving my List price in the Query. What I am attempting to do via a Query is create a report that would combine the information from the Product fields (Product-1, Product-2 & Product-3) and produce the following:

Qty Product UnitPrice ListPrice Serial #
1 AA 102 Product1 $1,234.34 $1,000.00 4374-1
4 DD 123 Product2 $1,133.22 $1,050.00 5612-1
5 $2,367.56 $2,050.00

The key point – I am trying to filter some information. In the example above, I would not need to see “AM/FM Radio” – but I would need all the products that are listed as ?? ??? (two characters space and three characters). This is generally the format of my product codes.

I have been able to create is a Report that that groups my different fields. But, I have not been able to filter the information I do not want. Currently, I get blank entries and some information I do not want. I am not sure if I accomplish this in my Query or the Report?

 

[Leo]

Is there a simple way to group data from different fields (still using the same table)?

Should I go back and restructure my table? I attempted the Grouping function - and its not working very well. One issue, is that I have too much empty space on my report.