How to incorporate Criteria in MS Access Query for a report

I have a report in a Access database called Invoices. How and where do I incorporate the following criteria in the query to meet these requirements. I am new to access.

Sub Accounts
BTT = 763679
BTI = 763665
STATS = 762925
IDS = 763919


Credit Obj Code
If DivisionCode=”BTT” or ”BTI” or “Stats” then it is = 473
Else DivisionCode =”IDS” which is 693
End If

Charge Obj Code
If DivisionCode=”BTT” or “BTI” or “Stats” then it is = 274
Else DivisionCode=”IDS” which is = 224
End if

Chrisjack001Asked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
yes but modify the table name first... which table has these columns

[Credit Obj Code]
[Charge Obj Code]

you should modify the tablename before using the query below
SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate
FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID
where
tablename.[Credit Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 473, 693)
and
tablename.[Charge Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 274, 224)

Open in new window

0
 
HainKurtSr. System AnalystCommented:
maybe this:

where
[Credit Obj Code] = iif(DivisionCode="BTT" or DivisionCode="BTI" or DivisionCode="Stats", 473, 693)
and
[Credit Obj Code] = iif(DivisionCode="BTT" or DivisionCode="BTI" or DivisionCode="Stats", 274, 224)

0
 
Chrisjack001Author Commented:
How about the Sub Accounts and how and where can I put that in the design view. I Am new to this
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HainKurtSr. System AnalystCommented:
post the query for report...
0
 
Helen FeddemaCommented:
Better still, post the database.  This kind of criteria tweaking needs real data to work with.
0
 
Helen FeddemaCommented:
You will need to set up multiple criteria rows to accommodate the various combinations of criteria.
0
 
Chrisjack001Author Commented:
This is the query for the report.

SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID;
0
 
Chrisjack001Author Commented:
Attached is the database.
Main-Clinical-Sciences-Accountin.accdb
0
 
HainKurtSr. System AnalystCommented:
i cannot open the db.. it gives invalid path for o:\biostats\....\main accounting original_be.accde
0
 
Chrisjack001Author Commented:
Attached is the database
Main-Clinical-Sciences-Accountin.accdb
0
 
HainKurtSr. System AnalystCommented:
I get similar error when trying to open it

c:\documents and settings\cjac11\desktop\main accounting original_be.accde" is not a valid path... dont know why it is checking that folder? do you have some external code/data in that acdde file?
0
 
HainKurtSr. System AnalystCommented:
oops, there are lots of linked tables pointing to that file
0
 
Chrisjack001Author Commented:
It is the Object Report thats called Invoices. Thats the one that should meet that requirement when you open it.
0
 
Chrisjack001Author Commented:
What should I do to send this DB to you. I thought I should just attach it.
0
 
HainKurtSr. System AnalystCommented:
try adding this to the query of report

where
tablename.[Credit Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 473, 693)
and
tablename.[Charge Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 274, 224)
0
 
Chrisjack001Author Commented:
Should I put this directly after the last line in this query.

SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate
FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID;
0
 
Chrisjack001Author Commented:
The Credit Obj Code and Charge Obj code are not from any specific table. It should just meet that criteria. Where do I incorporate the Sub Accounts from the requirement

Sub Accounts
BTT = 763679
BTI = 763665
STATS = 762925
IDS = 763919
0
 
Chrisjack001Author Commented:
I dont want the user to be prompted when trying to open the report. Based on your recommendation thats what it is doing now. I also noticed that IDS which is 693 and 224 is missing from the query.
0
 
Chrisjack001Author Commented:
Please help
0
 
Chrisjack001Author Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.