report formating/sorting problem

[bill4364]

The database is set up like this:

The vender table just has the venders name and a a number (1-8) which uses that to connect to the orders table which has lots of information about the orders from each vendor, the important stuff for me right now is the item ordered and the cost per unit of the order.

Now the item order is designated by an item number which relates it to the item list table, since the same item is ordered from different venders each UNIQUE item does not have a a UNIQUE code number or something like that ( for example there are 13 unique items but 34 entries on the items list page).

I need to make a report that lists each of the 13 Items, showing the vendors that sell the items and the price that each once charges.

I cant figure out how to make it so that ONLY ONE item name comes up, with the whole list of vendors under it, showing their individual prices. I get all 34 items one way (it will give "airframe fastener" 3 sections, one for each of the vendors that supply that product, and will list the same price for every single time that it was purchased from that vendor (the orders table has 170 entries, the same product was ordered from the same place many different time in some circumstances).

A little advice of how to go about this would be wonderful, I know it is probably hard the way I laid it out but if you even have a hunch of some way for me to organize it differently or set up so new keys or relations, or run a query a specific way that could help me out I'd appreciate it.


Thanks guys.

 

[Trevor G]

Welcome to the forum,

Suggestion, is create a query that will deal with all of the tables you want and all of the fields you need, dont worry about setting sorting etc here.

Then in the report section use the wizard to guide you as it will ask you for groupings etc so you can select your field to group on and also once designed you can look at the sortings you want with multiple fields.

Trevor

 

[bill4364]

Hey thanks for responding Trevor.

That sounds like it would work out, I did figure out another way to to it though.

I just made a fourth table that had only 13 entries with the names of the items, then I went through all 34 of the entries in the items table and on an added column set one number for each item, so all 3 of the airframe fasteners from my example had a number 1 added onto their entries.

Then I just added a relationship with those 2 and used the new table with just the 13 entries as the top sort, the vendors as a second group, and their prices as the third.

I just had to added something for each unique item that could be connected to all the other entries. Ha unfortunately I have been using the report wizard and until you said that I didn't look around in the report panel to see the big group and sort button. But I know now, thanks for the help.