Solved

How to incorporate Criteria in MS Access Query for a report

Posted on 2011-02-22
20
256 Views
Last Modified: 2012-05-11
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
Comment
Question by:Chrisjack001
  • 11
  • 7
  • 2
20 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34952595
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
 

Author Comment

by:Chrisjack001
ID: 34952705
How about the Sub Accounts and how and where can I put that in the design view. I Am new to this
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34952774
post the query for report...
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34953053
Better still, post the database.  This kind of criteria tweaking needs real data to work with.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34953056
You will need to set up multiple criteria rows to accommodate the various combinations of criteria.
0
 

Author Comment

by:Chrisjack001
ID: 34954073
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
 

Author Comment

by:Chrisjack001
ID: 34955071
Attached is the database.
Main-Clinical-Sciences-Accountin.accdb
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955153
i cannot open the db.. it gives invalid path for o:\biostats\....\main accounting original_be.accde
0
 

Author Comment

by:Chrisjack001
ID: 34955677
Attached is the database
Main-Clinical-Sciences-Accountin.accdb
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955826
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 51

Expert Comment

by:HainKurt
ID: 34955838
oops, there are lots of linked tables pointing to that file
0
 

Author Comment

by:Chrisjack001
ID: 34955876
It is the Object Report thats called Invoices. Thats the one that should meet that requirement when you open it.
0
 

Author Comment

by:Chrisjack001
ID: 34955890
What should I do to send this DB to you. I thought I should just attach it.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955913
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
 

Author Comment

by:Chrisjack001
ID: 34956142
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 250 total points
ID: 34956239
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
 

Author Comment

by:Chrisjack001
ID: 34956616
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
 

Author Comment

by:Chrisjack001
ID: 34956896
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
 

Author Comment

by:Chrisjack001
ID: 34956898
Please help
0
 

Author Closing Comment

by:Chrisjack001
ID: 35977383
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now