Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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

0
Chrisjack001
Asked:
Chrisjack001
  • 11
  • 7
  • 2
1 Solution
 
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
 
HainKurtSr. System AnalystCommented:
post the query for report...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
HainKurtSr. 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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 11
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now