One to many Relationship in one record

[BL]

Dear all,

I have a invoice table (Table 1) which keeps the invoice master and the
amount of sales. We then have a tax table (Table 2) to calculate the
applicable tax for each line item, as a result

Table 1 Table 2
Record1 :$1000 Tax1 $5
Tax2 $6
Tax5 $2
Tax9 $3

Record2 :$2000 Tax1 $10
Tax2 $12

Record3 :$3000 Tax1 $15
Tax5 $10
Tax7 $25

I am wondering how to write a query that give me each sale and the related
tax as one record and I am interest to know Tax1 and Tax2 of each sales while
the rest of them can be combined as “other†tax.

I am thinking to use the CrossTab query but the Column Header is dynamic.

Thank you in advance for all your advise.

BL

 

[KARL DEWEY]

Need to see the table and field names. Post sample data from both tables.

 

[BL]

Hi, Karl,

The Name of the table and field names as well as the sample data are listed
as below

Table Name :tbl_PMT(table1)
tbl_TAX_LINE(table2)


Field Name in tbl_PMT
pmt_rqst_nbr

Field Name in tbl_TAX_LINE
pmt_rqst_nbr
tax_rule_id
tax_amt

Sample Data in tbl_PMT
pmt_rqst_nbr
UUS003229
106908
110000

Sample Data in tbl_TAX_LINE
pmt_rqst_nbr tax_rule_id tax_amt
UUS003229 GEXCH 4,000
106908 GGST 1,000
106908 GPST 1,500
110000 GGST 990
110000 GPST 600
110000 GPSTUS 500

Thank you very much for your kind assistance.

BL

 

[KARL DEWEY]

am interest to know Tax1 and Tax2
GEXCH, GPST, GGST, GPSTUS
So which are Tax1 and Tax2?

 

[KARL DEWEY]

It will be something like this --
SELECT tbl_PMT.pmt_rqst_nbr, Sum(IIf([tax_rule_id] Like
"GPST*",[tax_amt],0)) AS Tax, Sum(IIf([tax_rule_id] Not Like
"GPST*",[tax_amt],0)) AS Other
FROM tbl_PMT LEFT JOIN tbl_TAX_LINE ON tbl_PMT.pmt_rqst_nbr =
tbl_TAX_LINE.pmt_rqst_nbr
GROUP BY tbl_PMT.pmt_rqst_nbr;

 

[BL]

Hi, Karl,

Thank you very much, that is what I need and I have modified the query as
stated. By the way, if there is any recommendation on the web I can learn
more about application of SQL.

Cheers,

BL

It will be something like this --
SELECT tbl_PMT.pmt_rqst_nbr, Sum(IIf([tax_rule_id] Like
"GPST*",[tax_amt],0)) AS Tax, Sum(IIf([tax_rule_id] Not Like
"GPST*",[tax_amt],0)) AS Other
FROM tbl_PMT LEFT JOIN tbl_TAX_LINE ON tbl_PMT.pmt_rqst_nbr =
tbl_TAX_LINE.pmt_rqst_nbr
GROUP BY tbl_PMT.pmt_rqst_nbr;

Hi, Karl,

The Name of the table and field names as well as the sample data are listed
as below

Table Name :tbl_PMT(table1)
tbl_TAX_LINE(table2)


Field Name in tbl_PMT
pmt_rqst_nbr

Field Name in tbl_TAX_LINE
pmt_rqst_nbr
tax_rule_id
tax_amt

Sample Data in tbl_PMT
pmt_rqst_nbr
UUS003229
106908
110000

Sample Data in tbl_TAX_LINE
pmt_rqst_nbr tax_rule_id tax_amt
UUS003229 GEXCH 4,000
106908 GGST 1,000
106908 GPST 1,500
110000 GGST 990
110000 GPST 600
110000 GPSTUS 500

Thank you very much for your kind assistance.

BL

 

[KARL DEWEY]

I am not familar with web sites.
I bought two books at about 45-50 dollars each and by trial and error
looking at design view and then SQL view. I still do not have the knowledge
of subqueries so I use joined queries.
The two books I bought I use as reference only. They are SQL Unleased by
Sams and MSCE: SQL Server 6.5 Administration Study Guide by Network Press.
The latter is a Microsoft Certified Professional Approved Study Guide.
Of course the syntax in these books are slightly different from Access. The
Sams also hightlights any difference that Oracle has.

Hi, Karl,

Thank you very much, that is what I need and I have modified the query as
stated. By the way, if there is any recommendation on the web I can learn
more about application of SQL.

Cheers,

BL

It will be something like this --
SELECT tbl_PMT.pmt_rqst_nbr, Sum(IIf([tax_rule_id] Like
"GPST*",[tax_amt],0)) AS Tax, Sum(IIf([tax_rule_id] Not Like
"GPST*",[tax_amt],0)) AS Other
FROM tbl_PMT LEFT JOIN tbl_TAX_LINE ON tbl_PMT.pmt_rqst_nbr =
tbl_TAX_LINE.pmt_rqst_nbr
GROUP BY tbl_PMT.pmt_rqst_nbr;

Hi, Karl,

The Name of the table and field names as well as the sample data are listed
as below

Table Name :tbl_PMT(table1)
tbl_TAX_LINE(table2)


Field Name in tbl_PMT
pmt_rqst_nbr

Field Name in tbl_TAX_LINE
pmt_rqst_nbr
tax_rule_id
tax_amt

Sample Data in tbl_PMT
pmt_rqst_nbr
UUS003229
106908
110000

Sample Data in tbl_TAX_LINE
pmt_rqst_nbr tax_rule_id tax_amt
UUS003229 GEXCH 4,000
106908 GGST 1,000
106908 GPST 1,500
110000 GGST 990
110000 GPST 600
110000 GPSTUS 500

Thank you very much for your kind assistance.

BL