Solved

How to incorporate Criteria in MS Access Query for a report

Posted on 2011-02-22
20
287 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 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