Find annual totak issues per year from a table of transactionsr

[DaveT99]

Is there a better way



I was given a spare parts dataset with 3 columns, Stock No, Date Issued and Qty issued for 2017, 2018 & 2019

The aim was to obtain the usage for each year for each spare

I achieved, but usng a long winded approach i.e. over 4 steps

Step 1 I created a new column called YEAR, and I ran 3 separate select queries to assign the year in the new column for each issue (transaction)

Step 2 I created a cross tab query to see the quantity for each year

Step 3 I converted the cross tab query output into table, using a make table query

Step 4 I then linked the quantity per year table (new) with a 2^nd table containing other data like unit price, etc

Can anyone suggest if this process can be streamlined a little

[June7]

Do you really want the CROSSTAB display?

Why would you need 3 queries to assign year to new column?

Steps 1 and 2 might be done in a single query.

A CROSSTAB query can join to other tables/query so Step 3 might not be needed.

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

[DaveT99]

Not sure if I have done this correct, but thanks for such a quick reply
Please access file as a zip
Thanks again
Dave

[DaveT99]

have attached a word doc with examples of what I did

[June7]

Year is a reserved word (it is a function). Should not use reserved words as names for anything.

A simple aggregate query will sum the Qty field by year for each part:

SELECT tbl_Issues.Stock_No, Year([Issue_Date]) AS Yr, Sum(tbl_Issues.Qty) AS SumOfQty, Avg(tbl_Issues.Unit_Cost) AS AvgUnitCost
FROM tbl_Issues
GROUP BY tbl_Issues.Stock_No, Year([Issue_Date]);

If you want to pivot the data so each year is a column header:

TRANSFORM Sum(tbl_Issues.Qty) AS SumOfQty
SELECT tbl_Issues.Stock_No
FROM tbl_Issues
GROUP BY tbl_Issues.Stock_No
PIVOT Year([Issue_Date]);

More info about CROSSTAB http://allenbrowne.com/ser-67.html

Use the query design tools on the ribbon.

If this is not what you want, then provide sample of desired output.

There is only 1 table in the posted db.

[DaveT99]

Thanks you so very very much June7, for such a quick response. Your solutions have exteded my Access knowledge, especially the use of the expression Yr: Year([Issue_Date])
Sorry forgot to include the tble SPD1, but you have given me two perfect solutions
Dave