How to query results on a report

[jleist]

I am relatively new to Access and I am using the 2003 version.

Here is my issue:

I have a report that is a summary of the products we manufacture. On the report I want it to list the components needed to make the item. I am using one master table to pull the information from. However, when I run the report it doesnt list the different components, it gives a new page for each component and i dont want that. Thanks for your help in advance! Hope this isnt confusing.

[beacon]

Hi jliest,

Welcome to Bytes!

I think I can help you out, but first will you post the fields on your master table?

Thanks,
beacon

[jleist]

it's a lot.... i am dealing with the Item Number and the Component Item Number. Each Item Number might be associated with anywhere from 0-20 different Component Item Numbers so there can be 0-20 of the same Item Number listed. I just want a list of the Component Item Numbers for the Item Number they pertain to.

Does this make sense?

Thanks,

JLeist

[beacon]

I don't think you necessarily need a query to get this to work. You should be able to just add a group level for the Item Number to your report.

In case you aren't familiar with grouping, right-click the Details bar in Design View of your report, then select Sorting and Grouping. When the dialog box opens, click the dropdown and choose Item Number. Click Ok and check to make sure that your Item Number field is in the new Item Number group. If it's not there, drag it there and try running your report.

[jleist]

Thanks! Now I have a bigger issue. How do I get ride of blank space? I have sales information that is being hidden when Is Null but it leaves a bunch of space and my components listed for each item number are being spaced too far out. it's on 11x17 paper. So I want component information listed on the left and the sales information on the right but for each component listed there is a bunch of space where the sales information is hidden because it Is Null. Does this make sense? Thanks!

[jleist]

Also... apparently my code to hide sales information for the components did not work, it reappears at the top of each page. I think I will I need a separate table...what do you think? One that contains component information and one that contains Sales information in order to get both pieces of information on there without it repeating itself...?

Right now this is a snippet of the table I have

Item Number / Sales / Component Item Number
XXX / 10 / ABC-456
XXX / 10 / DEF-456
XXX / 10 / GHI-456
XXX / 10 / JKL-456
YYY / 10 / 567-ABC
YYY / 10 / 458-YGT
YYY / 10 / 345-GHI

Where the sales information only pertains to the item number once but is repeated because of the number of components associated with the Component Item Number

[beacon]

For the first question, I'm confused...wher e is the sales information being hidden at, on the report or on a query? And did you write code to combine two fields together? Maybe if you posted your code it would make sense.

For the second question, yes, you should create another table. The way you described your database, it doesn't adhere to the rules of normalization, which will make it very difficult for you to manipulate data efficiently and effectively. I suggest creating a table for the items, a table for the components, and another table to link the two together (a one-to-many relationship since one item may have many components). I would also use your current table, or create a new one and delete your current one, for the sales info and link it to the items table (probably another one-to-many relationship since one sale likely involves many items).