Calculating Total Through Query

[LucianoPena28]

Hey guys,

I'm a bit rusty at Microsoft Access, but I've been tasked to create a database through Access for a very small department where I work for but I'm having difficulty calculating my Total on the Query "Total Amount".

I have an IF Function checking and also calculating the GST + Total Cost of the amount of Items being ordered.

As we all know, GST is 12.5%, but I keep getting errors.

So far I have

Total: IIf([GST_Application].[GST_Applied]='Yes',[Procurement].[Item_Amount]*[Items_Table].[Item_Price]*0.125,[Procurement].[Item_Amount]*[Items_Table].[Item_Price])

Can someone show me how to make this a bit better?

I also want to calculate the Total of all Items being ordered. I also want to display these on the Main Page Form I have created on. I also want to display for "Fiscal Year" a drop down with options of a condition. These options should show if the dates are between April 1 - March 31, the format Year of April 1st to Year March 31st, or "Year - Year". I'm stuck on that too. If someone can help, that would be amazing!

I will include the database in an attachment.

Please Find Attachment named "GI3 PROCUREMENT"

[June7]

GST_Applied is a Yes/No data type so don't use apostrophe delimiters for Boolean constant.

That expression is not adding anything.

GST: [Item_Amount]*[Item_Price]*IIf([GST_Applied]=Yes, 0.125, 0)

Then need to add GST to cost.

Total: [Item_Amount]*[Item_Price] + GST

Your table relationships have circular references and this is carrying over to query design. Fix the query to eliminate circular links.
Review https://www.codeproject.com/articles...atabase-design
Suggest remove GST_Application and Supplier links to Procurement.

You want to calculate the Total of all Items being ordered for what - one supplier? Main Page is bound to Users table, so calculations on ordered items will likely involve DSum() Domain Aggregate function.

Need to calculate a field that assigns fiscal year to each record. Then that field can be used for filtering records by fiscal year. And I see you are calculating FY from the year of Indent_Date - why, what is this date for?

I am not entirely clear what you want the combobox to list. Perhaps provide a sample here in post. Use Advanced post editor to build a table.

Strongly advise not to use spaces in naming convention.

[LucianoPena28]

GST_Applied is a Yes/No data type so don't use apostrophe delimiters for Boolean constant.

That expression is not adding anything.

GST: [Item_Amount]*[Item_Price]*IIf([GST_Applied]=Yes, 0.125, 0)

Then need to add GST to cost.

Total: [Item_Amount]*[Item_Price] + GST

Your table relationships have circular references and this is carrying over to query design. Fix the query to eliminate circular links.
Review https://www.codeproject.com/articles...atabase-design
Suggest remove GST_Application and Supplier links to Procurement.

You want to calculate the Total of all Items being ordered for what - one supplier? Main Page is bound to Users table, so calculations on ordered items will likely involve DSum() Domain Aggregate function.

Need to calculate a field that assigns fiscal year to each record. Then that field can be used for filtering records by fiscal year. And I see you are calculating FY from the year of Indent_Date - why, what is this date for?

I am not entirely clear what you want the combobox to list. Perhaps provide a sample here in post. Use Advanced post editor to build a table.

Strongly advise not to use spaces in naming convention.

Thank you very much June7, but now I want to calculate the Total SUM of all Items in the query per year selected in from the Drop Down selection in the "Main Page" Form.

Should I create a new Query? What would be the best formula to have this calculated. I want to calculate the total for Year 2021, 2022, etc which can be selected from the DropDown.

[June7]

Try this expression in textbox.

=DSum("Total","Total Amount","Year(Indent_Date)=" & Nz([Combo17],0))

[LucianoPena28]

Try this expression in textbox.

=DSum("Total","Total Amount","Year(Indent_Date)=" & Nz([Combo17],0))

I entered the expression in a textbox, but it didn't retrieve any results.

[LucianoPena28]

Nevermind, it worked. But what about the dropdown? I want it to reflect based on a date range (Between April 1st - March 31st of the following year) to show as Year to Year or Year - Year, e.g: 2021-2022.

[June7]

Didn't answer my question about Indent_Date field and the fiscal year calculation in Fiscal Year query.

How do you determine what fiscal year a record belongs to? Consider this calculation in [Total Amount] query:
FY: Year([Indent_Date])+IIf(Month([Indent_Date])>3,1,0)

Combobox properties:
RowSource: SELECT DISTINCT FY, FY-1 & "-" & FY FROM [Total Amount];
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1

[LucianoPena28]

To determine what fiscal year a record belongs to, the record's indent_date should be either between/equal to April 1st of the current year, e.g 2022, and or between/equals to date March 31st of the following year, e.g 2023.

I hope this answers your question

[June7]

Okay, did you try the code I suggested?

[LucianoPena28]

Yes, I did, but it's not displaying "2021-2022, 2022-2023, etc."

The idea of the combo-box is to select from a dropdown.

I've recreated the database on a different angle, but I kept the original one just in case I found a better way to create this database. In the attachment, I've included the original database that was used before, in Excel, but it only provides a 1D database and it's not very user friendly.

Please assist me with this for we don't have the resources as of yet to invest in additional software to perform these tasks, and we are using what we have, hence Excel and Access, to monitor our data.

See new attachments below:
GI3 PROCUREMENT.zip

Also see screenshot below for a more detailed explanation of this query.

[LucianoPena28]

You can also contact me via skype, my skype username is: live:lucianojaimepena. To make things easier for anyone who can aid me, I've created an invite link: https://join.skype.com/invite/vRkosTCUZVHO

I'm sharing the files temporarily through OneDrive if that helps updating the files better.

See link: https://1drv.ms/u/s!AlU6SiABjq4y00bi...C19F-?e=MhngE3 & https://1drv.ms/x/s!AlU6SiABjq4y00Iv...qo2Vq?e=igPEDq

[June7]

The textbox expression would now use the FY calculated field:
=DSum("Total","Total Amount","FY=" & Nz([cmboYear_select],0))
Your "original" works.

If you want to allow filtering on date range that is not the fixed fiscal year, review http://allenbrowne.com/ser-62.html

No, I won't contact via Skype. And I prefer not to update your files. Follow guidance provided here.

Many forum reviewers will not download files from other sites.