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
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
ASKER
How about the Sub Accounts and how and where can I put that in the design view. I Am new to this
post the query for report...
Better still, post the database. This kind of criteria tweaking needs real data to work with.
You will need to set up multiple criteria rows to accommodate the various combinations of criteria.
ASKER
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;
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;
ASKER
Attached is the database.
Main-Clinical-Sciences-Accountin.accdb
Main-Clinical-Sciences-Accountin.accdb
i cannot open the db.. it gives invalid path for o:\biostats\....\main accounting original_be.accde
ASKER
Attached is the database
Main-Clinical-Sciences-Accountin.accdb
Main-Clinical-Sciences-Accountin.accdb
I get similar error when trying to open it
c:\documents and settings\cjac11\desktop\ma
c:\documents and settings\cjac11\desktop\ma
oops, there are lots of linked tables pointing to that file
ASKER
It is the Object Report thats called Invoices. Thats the one that should meet that requirement when you open it.
ASKER
What should I do to send this DB to you. I thought I should just attach it.
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)
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)
ASKER
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;
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sub Accounts
BTT = 763679
BTI = 763665
STATS = 762925
IDS = 763919
ASKER
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.
ASKER
Please help
ASKER
Thanks
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)