Query Criteria

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi All,
1. Currently I'm using below query to show me tax amount
of sales. Main field in this query is DEFAULT_TAX_CODE
which is basicly only got for type of data (F,Z,E,T).
There is also TAX_AMT field which prints the tax amount. I
need this query to show the tax amount(TAX_AMT) in 4
different column(F,T,E,T). Something like,
if (TAX_AMT)is F then print the (TAX_AMT) on column A,
if (TAX_AMT)is Z then print the (TAX_AMT) on column B,
if (TAX_AMT)is E then print the (TAX_AMT) on column C,
if (TAX_AMT)is T then print the (TAX_AMT) on column D,
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

2. How can I set a dialog box where when the user run this
query, to ask them what month of query you want to view.
The date field is [APTRANS Query].DOC_DATE as shown above.

Please help me guys. I really appreciate your help.

Best Regards,
Mark
 
Thank You Duane for your kindness,
I created below shown query using crostab wizard.
I want to add another column [APTRANS Query].AMT into this
query. How can I do this? Pls help.

Regards,
Mark

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT]
FROM GST
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

-----Original Message-----
You could do this with a crosstab query where DEFAULT_TAX_CODE is the column
heading, most of your other fields are Row Headings, and the Value column is
TAX_AMT.
Create a form and combo box (frmRptMth!cboMonth). Set the Row Source Type to
Value List and enter a Row Source of 1,2,3...12.
In query design, select Query|Parameters and enter
Forms!frmRptMth!cboMonth Integer
Then add a column to the query grid:
RptMth:Month(DOC_DATE)
criteria:
Forms!frmRptMth!cboMonth

--
Duane Hookom
MS Access MVP


Mark said:
Hi All,
1. Currently I'm using below query to show me tax amount
of sales. Main field in this query is DEFAULT_TAX_CODE
which is basicly only got for type of data (F,Z,E,T).
There is also TAX_AMT field which prints the tax amount. I
need this query to show the tax amount(TAX_AMT) in 4
different column(F,T,E,T). Something like,
if (TAX_AMT)is F then print the (TAX_AMT) on column A,
if (TAX_AMT)is Z then print the (TAX_AMT) on column B,
if (TAX_AMT)is E then print the (TAX_AMT) on column C,
if (TAX_AMT)is T then print the (TAX_AMT) on column D,
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

2. How can I set a dialog box where when the user run this
query, to ask them what month of query you want to view.
The date field is [APTRANS Query].DOC_DATE as shown above.

Please help me guys. I really appreciate your help.

Best Regards,
Mark
Click to expand...


.
Click to expand...
 
I'm afraid to ask but do you need this by DEFAULT_TAX_CODE or just one
column. If just one column I would have to guess on how APTRANS Query is
related to GST.

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT], Sum([APTRANS Query].AMT) as SumAmt
FROM GST INNER JOIN [APTRANS Query] ON
[GST].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

--
Duane Hookom
MS Access MVP


Mark said:
Thank You Duane for your kindness,
I created below shown query using crostab wizard.
I want to add another column [APTRANS Query].AMT into this
query. How can I do this? Pls help.

Regards,
Mark

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT]
FROM GST
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

-----Original Message-----
You could do this with a crosstab query where DEFAULT_TAX_CODE is the column
heading, most of your other fields are Row Headings, and the Value column is
TAX_AMT.
Create a form and combo box (frmRptMth!cboMonth). Set the Row Source Type to
Value List and enter a Row Source of 1,2,3...12.
In query design, select Query|Parameters and enter
Forms!frmRptMth!cboMonth Integer
Then add a column to the query grid:
RptMth:Month(DOC_DATE)
criteria:
Forms!frmRptMth!cboMonth

--
Duane Hookom
MS Access MVP


Mark said:
Hi All,
1. Currently I'm using below query to show me tax amount
of sales. Main field in this query is DEFAULT_TAX_CODE
which is basicly only got for type of data (F,Z,E,T).
There is also TAX_AMT field which prints the tax amount. I
need this query to show the tax amount(TAX_AMT) in 4
different column(F,T,E,T). Something like,
if (TAX_AMT)is F then print the (TAX_AMT) on column A,
if (TAX_AMT)is Z then print the (TAX_AMT) on column B,
if (TAX_AMT)is E then print the (TAX_AMT) on column C,
if (TAX_AMT)is T then print the (TAX_AMT) on column D,
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

2. How can I set a dialog box where when the user run this
query, to ask them what month of query you want to view.
The date field is [APTRANS Query].DOC_DATE as shown above.

Please help me guys. I really appreciate your help.

Best Regards,
Mark
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Duane,
Just need one column for [APTRANS Query].AMT. Below are
the GST query & other related query as you requested. You
doin great. Thank You Duane. Pls help.

Mark

GST Query:-
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

APSUPPS Query:-
SELECT APSUPPS.SUPPLIER_NO, APSUPPS.NAME1,
APSUPPS.DEFAULT_TAX_CODE, APSUPPS.FOR_CURR_CODE
FROM APSUPPS;

APLAND Query:-
SELECT APTRANS.FOR_CURR_CODE, APTRANS.SUPPLIER_NO,
APTRANS.DOC_DATE, APTRANS.DOC_TYPE, APTRANS.DOC_NO,
APTRANS.AMT, APTRANS.POSTING_DATE, APTRANS.FOR_CURR_RATE,
APTRANS.FOR_CURR_AMT, APTRANS.TAX_AMT
FROM APTRANS
WHERE (((APTRANS.DOC_TYPE)="I") AND ((APTRANS.POSTING_DATE)
=#7/2/2003#))
ORDER BY APTRANS.POSTING_DATE;



-----Original Message-----
I'm afraid to ask but do you need this by DEFAULT_TAX_CODE or just one
column. If just one column I would have to guess on how APTRANS Query is
related to GST.

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT], Sum([APTRANS Query].AMT) as SumAmt
FROM GST INNER JOIN [APTRANS Query] ON
[GST].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

--
Duane Hookom
MS Access MVP


Mark said:
Thank You Duane for your kindness,
I created below shown query using crostab wizard.
I want to add another column [APTRANS Query].AMT into this
query. How can I do this? Pls help.

Regards,
Mark

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT]
FROM GST
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

-----Original Message-----
You could do this with a crosstab query where DEFAULT_TAX_CODE is the column
heading, most of your other fields are Row Headings,
Click to expand...
and
the Value column is
TAX_AMT.
Create a form and combo box (frmRptMth!cboMonth). Set
Click to expand...
the
Row Source Type to
Value List and enter a Row Source of 1,2,3...12.
In query design, select Query|Parameters and enter
Forms!frmRptMth!cboMonth Integer
Then add a column to the query grid:
RptMth:Month(DOC_DATE)
criteria:
Forms!frmRptMth!cboMonth

--
Duane Hookom
MS Access MVP


Hi All,
1. Currently I'm using below query to show me tax amount
of sales. Main field in this query is DEFAULT_TAX_CODE
which is basicly only got for type of data (F,Z,E,T).
There is also TAX_AMT field which prints the tax amount. I
need this query to show the tax amount(TAX_AMT) in 4
different column(F,T,E,T). Something like,
if (TAX_AMT)is F then print the (TAX_AMT) on column A,
if (TAX_AMT)is Z then print the (TAX_AMT) on column B,
if (TAX_AMT)is E then print the (TAX_AMT) on column C,
if (TAX_AMT)is T then print the (TAX_AMT) on column D,
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

2. How can I set a dialog box where when the user run this
query, to ask them what month of query you want to view.
The date field is [APTRANS Query].DOC_DATE as shown above.

Please help me guys. I really appreciate your help.

Best Regards,
Mark





.
Click to expand...
Click to expand...


.
Click to expand...
 
So, did you try the query that I suggested?

--
Duane Hookom
MS Access MVP


Mark said:
Hi Duane,
Just need one column for [APTRANS Query].AMT. Below are
the GST query & other related query as you requested. You
doin great. Thank You Duane. Pls help.

Mark

GST Query:-
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

APSUPPS Query:-
SELECT APSUPPS.SUPPLIER_NO, APSUPPS.NAME1,
APSUPPS.DEFAULT_TAX_CODE, APSUPPS.FOR_CURR_CODE
FROM APSUPPS;

APLAND Query:-
SELECT APTRANS.FOR_CURR_CODE, APTRANS.SUPPLIER_NO,
APTRANS.DOC_DATE, APTRANS.DOC_TYPE, APTRANS.DOC_NO,
APTRANS.AMT, APTRANS.POSTING_DATE, APTRANS.FOR_CURR_RATE,
APTRANS.FOR_CURR_AMT, APTRANS.TAX_AMT
FROM APTRANS
WHERE (((APTRANS.DOC_TYPE)="I") AND ((APTRANS.POSTING_DATE)
=#7/2/2003#))
ORDER BY APTRANS.POSTING_DATE;



-----Original Message-----
I'm afraid to ask but do you need this by DEFAULT_TAX_CODE or just one
column. If just one column I would have to guess on how APTRANS Query is
related to GST.

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT], Sum([APTRANS Query].AMT) as SumAmt
FROM GST INNER JOIN [APTRANS Query] ON
[GST].SUPPLIER_NO = [APTRANS Query].SUPPLIER_NO
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;

--
Duane Hookom
MS Access MVP


Mark said:
Thank You Duane for your kindness,
I created below shown query using crostab wizard.
I want to add another column [APTRANS Query].AMT into this
query. How can I do this? Pls help.

Regards,
Mark

TRANSFORM Sum(GST.TAX_AMT) AS SumOfTAX_AMT
SELECT GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO, Sum
(GST.TAX_AMT) AS [Total Of TAX_AMT]
FROM GST
GROUP BY GST.SUPPLIER_NO, GST.NAME1, GST.DOC_NO
PIVOT GST.DEFAULT_TAX_CODE;


-----Original Message-----
You could do this with a crosstab query where
DEFAULT_TAX_CODE is the column
heading, most of your other fields are Row Headings, and
the Value column is
TAX_AMT.
Create a form and combo box (frmRptMth!cboMonth). Set the
Row Source Type to
Value List and enter a Row Source of 1,2,3...12.
In query design, select Query|Parameters and enter
Forms!frmRptMth!cboMonth Integer
Then add a column to the query grid:
RptMth:Month(DOC_DATE)
criteria:
Forms!frmRptMth!cboMonth

--
Duane Hookom
MS Access MVP


Hi All,
1. Currently I'm using below query to show me tax amount
of sales. Main field in this query is DEFAULT_TAX_CODE
which is basicly only got for type of data (F,Z,E,T).
There is also TAX_AMT field which prints the tax
amount. I
need this query to show the tax amount(TAX_AMT) in 4
different column(F,T,E,T). Something like,
if (TAX_AMT)is F then print the (TAX_AMT) on column A,
if (TAX_AMT)is Z then print the (TAX_AMT) on column B,
if (TAX_AMT)is E then print the (TAX_AMT) on column C,
if (TAX_AMT)is T then print the (TAX_AMT) on column D,
SELECT [APSUPPS Query].SUPPLIER_NO, [APSUPPS
Query].NAME1,
[APSUPPS Query].DEFAULT_TAX_CODE, [APTRANS
Query].DOC_NO,
[APTRANS Query].DOC_DATE, [APTRANS Query].POSTING_DATE,
[APSUPPS Query].FOR_CURR_CODE, [APTRANS
Query].FOR_CURR_RATE, [APTRANS Query].AMT, [APTRANS
Query].TAX_AMT
FROM [APSUPPS Query] INNER JOIN [APTRANS Query] ON
[APSUPPS Query].SUPPLIER_NO = [APTRANS
Query].SUPPLIER_NO
ORDER BY [APSUPPS Query].SUPPLIER_NO;

2. How can I set a dialog box where when the user run
this
query, to ask them what month of query you want to view.
The date field is [APTRANS Query].DOC_DATE as shown
above.

Please help me guys. I really appreciate your help.

Best Regards,
Mark





.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top