Query finding sales by date

[v!ctor]

Hello,


I have a table with item transactions that are by type (Addition/Removal) and by date.

I want to create a query so to have all removals (sales) by month.

Is it possible?

Thanks

[orange]

What are the table fields?
What query syntax have you tried? Can you post the SQL?

[v!ctor]

In the table "transactions'' I have the following columns
Item / Transaction Type(Add or removal) / Date / Quantity

Also I Have a Table with the "Inventory" and one more with the "transactions types".

I want to create a query like this:

Item / Description / Jan / Feb / March / ... In each month I want only the sales (removals)
e.g
001 / ABC / 20 / 30 / 22 / ...
002 / ABCD / 13 / 19 / 20 / ...

Hope to be clear
I tried to upload mu file but it is 1,5mb and there is an upload limit

[alansidman]

I tried to upload mu file but it is 1,5mb and there is an upload limit

Suggest you run a compact and repair, then zip the file. Upload the zipped file.

[v!ctor]

zip file uploaded.

[alansidman]

The first issue I noted in your db is that you have lookups in your tables. This is a big no-no. Look at this site explaining why.

http://access.mvps.org/access/lookupfields.htm

If you continue down this road you will continue to encounter issues. It just plain doesn't work. I get mismatch issues when I try to create your query. I don't see any forms for inputting data into your tables. This is the second issue in a poorly designed database. Create forms that are bound to your tables. Use the forms to input data. Put your lookups in combo boxes in your forms. You should not be entering data directly into tables not should your users ever ever see a table.

Fix these issues and then come back to us for assistance with your query and you will be on the right track to a sound database schema. I apologize for appearing harsh, but good db development is key to getting good results. There is an old mantra, GIGO, ie. Garbage In, Garbage Out. Same goes for the design.

Alan

[v!ctor]

I made some changes according of what you suggested.
I delete lookup from "transactions" table and I create a form entering the data.

Is it possible now to find the sales by month?

Thank you in advnace

[alansidman]

I removed your lookup from your table as it was still causing issues. It remains in your form. I then built your query to include only removals and added a parameter for the dates. Look at the query to see how it is set up.

[v!ctor]

Hello,

Thanks for your help...
I would like to ask you one more question
Is it possible to use in my query "Sales by month" a folmula in a column like "sumif "?
Sum column "quantity" if date is between two specific dates
e.g
Item ID / Description / January / February / March / ...
10 / ABC / Qty /
20 / ABCD / Qty /

In the column "Qty" to have a formula like: January: Sum(Iff [Transactions]![Date] = between [date] and [date]; Quantity;0)
If it possible how will be tis formula?

p.s beginner in Access

[alansidman]

Attached I added two new queries to the db. It uses the aggregate feature available. In the setup query, I had to have Access determine the month so that you could easily filter by month. I used the choose function to allow this to occur. I then created a new query that used the setup query as its record source and created an aggregate query. I did not add the description as a field to group on, but you can.