Query has data, Report shows zeros

[smeghead67]

Hello,



I created an inventory control DB for my work. To show each item and a total, I have a query that gets the total of each part for each storage area and a total of that part. it also takes the valuation of each part and gives me a $ total. When i run this query, I get the current totals.

MFR     PN              Part                 Total   Lodge   Dungeon   Barn    Price each  Total Value
IGT     500123        reel widget              5      2         1        1       $ .75        $3.75

When i create a report using the report button on the create menu, it gives me all the fields from the report, but the totals are 0. it displays the item id number, name and price for one unit correctly, but the total columns for the storage areas and total of all parts and the $ total are zeros.

MFR     PN              Part                 Total   Lodge   Dungeon   Barn    Price each  Total Value
IGT     500123        reel widget              0      0         0         0       $ .75        $0.00

Am I missing something?

[alansidman]

In your report, in design view, right click on the fields that have the incorrect data. Select Properties. Check the record source for each of the items and make sure that it is the query.

[June7]

Actually, think need to click on the textboxes and look at ControlSource property. Very odd.

Show the sql statement of the report RecordSource. Is it an aggregate (GROUP BY) query?

[smeghead67]

the report gets its info from the query invadmin

SELECT partqty.mfr, partqty.pn, partqty.description, partqty.purchasetype, partqty.total, partqty.Lodge, partqty.Track, partqty.Inventory, partqty.Mezz, partqty.Clubhouse, partqty.AppleBarn, partqty.WarehouseNorth, partqty.Bench, partqty.OutRepair, partqty.valueper, IIf([purchasetype]=2,0,(((([Lodge]+[Track]+[Inventory]+[Mezz]+[Clubhouse]+[AppleBarn]+[WarehouseNorth])*[valueper]))+(([Bench]+[OutRepair])*([valueper]*0.3333333333)))*[unitsize]) AS valuetotal, partqty.accounttype
FROM partqty
WHERE (((partqty.administrative)=Yes))
ORDER BY partqty.mfr;

now the partqty query uses subquerys is this the source of the problem with the report?

partqty

SELECT Parts.pn, Parts.description, Parts.valueper, Parts.unitsize, Parts.reorder, Parts.repairable, Parts.purchasetype, Parts.iscritical, Parts.administrative, Parts.ledger, Parts.mfr, Parts.parttype, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID))) AS Tot, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "L*")) AS Ldge, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "B*")) AS Bnch, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "T*")) AS Trk, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "I*")) AS Inv, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "A*")) AS AB, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "M*")) AS Mzz, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "C*")) AS Club, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "N*")) AS WN, (SELECT SUM([part2].[qty])
    FROM partstore AS part2 
    WHERE (((part2.pid)=Parts.ID)) AND ((part2.binid) Like "O*")) AS OFR, IIf(IsNull([Tot]),0,[Tot]) AS Total, IIf(IsNull([Ldge]),0,[Ldge]) AS Lodge, IIf(IsNull([Bnch]),0,[Bnch]) AS Bench, IIf(IsNull([Trk]),0,[Trk]) AS Track, IIf(IsNull([Inv]),0,[Inv]) AS Inventory, IIf(IsNull([AB]),0,[AB]) AS AppleBarn, IIf(IsNull([Mzz]),0,[Mzz]) AS Mezz, IIf(IsNull([Club]),0,[Club]) AS Clubhouse, IIf(IsNull([WN]),0,[WN]) AS WarehouseNorth, IIf(IsNull([OFR]),0,[OFR]) AS OutRepair, Parts.ID, Parts.accounttype
FROM Parts;

Ted

[smeghead67]

Alansidman and June,

the record source is the correct query.

[June7]

I've never built query with subqueries to return single value but all looks like should work.

If you want to provide db for analysis, follow instructions at bottom of my post.