Calculations using the fields of a crosstab query.

[ser01]

I have a crosstab query that processes and displays a dataset. The main table that holds the data for the query has several fields. One of them is the items sold, another one is the date for a particular week, a third one holds the quantity. This is repeated for each week of the year, the date being the end of the week. What the query does, is only display the data for the dates I choose, and labels the quantities field with their respective dates. I have all of the above working, and the SQL is generated from VBA. Next i need to perform some simple calculations with the data from one of the weeks. This is where i hit a snag. I have no idea how to identify the field i need to use in the calculation. Here is my SQL:

TRANSFORM Sum(tblSales.Quantity) AS SumOfQuantity
SELECT tblInventory.Code, tblInventory.Item, tblInventory.[In Stock], Sum(tblSales.Quantity) AS Total
FROM tblVendors INNER JOIN (tblInventory INNER JOIN tblSales ON tblInventory.Code = tblSales.[Item Code]) ON tblVendors.Item = tblInventory.Item
WHERE (((tblSales.Dates)=#6/9/2009# Or (tblSales.Dates)=#6/16/2009#) AND ((tblVendors.Vendor)="FESTIVAL"))
GROUP BY tblInventory.Code, tblInventory.Item, tblInventory.[In Stock], tblVendors.Vendor
PIVOT tblSales.Dates;

So i need to have a calculated field that does a simple calculation on the first week displayed. The calculation is quantity from first week/7*3.
Any help or suggestions would be greatly appreciated.

[ser01]

Any suggestions at all?

[Buakaw]

Is it possible to put up a screen shot of your crosstab query, so it'll be easier to understand what you're trying to do?

[ser01]

Is it possible to put up a screen shot of your crosstab query, so it'll be easier to understand what you're trying to do?

Thank you for your reply. Below see a screenshot of the query. I would like to populate the [Calculation] filed with data that is a result of one the [Date] fields /7*3.
I could just have a create table query and put all the calculations I would need as fields in a new table. This just seems very inelegant, and I was hoping someone would be able to point me to a better solution. Please let me know if any other info is needed to clarify the problem.

[Buakaw]

Thank you for your reply. Below see a screenshot of the query. I would like to populate the [Calculation] filed with data that is a result of one the [Date] fields /7*3.
I could just have a create table query and put all the calculations I would need as fields in a new table. This just seems very inelegant, and I was hoping someone would be able to point me to a better solution. Please let me know if any other info is needed to clarify the problem.

In design view, you can try adding another column thus:

Calculation: <table or query name>.Date / 7 * 3

It works for Sum(), try it with this calculation and see if it does what you want.