relationship

[Guest]

I have two tables ... case and cashhst that I import from a db file. the
relationship is fairdate.case to fairdate.cashhst. Case has the columns
fairdate, case and booth and cashhst has fairdate and case (along with a few
other, these are the ones of concern).

When I do a query, SQL follows

SELECT cashhst.FAIRDATE, cashhst.CASETYPE, cashhst.CASE, CASE.BOOTH,
[numtravchg]*[amttravchg] AS trav, [hundredchg]*100 AS hundred, [fiftychg]*50
AS fifty, [twentychg]*20 AS twenty, [tenchg]*10 AS ten, [fivechg]*5 AS five,
[twochg]*2 AS two, [onechg]*1 AS one, [cent100chg]*1 AS onecoin,
[cent50chg]*0.5 AS 50coin, [cent25chg]*0.25 AS 25coin, [cent10chg]*0.1 AS
10coin, [cent05chg]*0.05 AS 5coin, [cent01chg]*0.01 AS 1coin, [roll50chg]*10
AS 50roll, [roll25chg]*10 AS 25roll, [roll10chg]*5 AS 10roll, [roll05chg]*2
AS 5roll, [roll01chg]*0.5 AS 1roll,
[trav]+[hundred]+[fifty]+[twenty]+[ten]+[five]+[two]+[one]+[50coin]+[25coin]+[10coin]+[5coin]+[1coin]+[50roll]+[25roll]+[10roll]+[5roll]+[1roll]
AS broughtin,
[inter1chg]+[inter2chg]+[inter3chg]+[inter4chg]+[inter5chg]+[inter6chg]+[inter7chg]
AS totalcounted, [broughtin]+[totalcounted]-210 AS totalcashsold,
[totalcounted]/[totalcashsold] AS percentage
FROM cashhst INNER JOIN [CASE] ON cashhst.FAIRDATE = CASE.FAIRDATE;

I'm getting the same case number for each booth, so it's giving me over 3
million records. Is there anyway to write in the SQL, to match case and
fairdate and give me a booth number?

Thanks for any help

 

[Guest]

Try this ---
FROM (cashhst INNER JOIN [CASE] ON cashhst.FAIRDATE = CASE.FAIRDATE) AND
(cashhst INNER JOIN [CASE] ON cashhst.Case = CASE.Case);

 

[John W. Vinson]

I'm getting the same case number for each booth, so it's giving me over 3
million records. Is there anyway to write in the SQL, to match case and
fairdate and give me a booth number?

Join in the grid by both fields, or equivalently

FROM cashhst INNER JOIN [CASE] ON cashhst.FAIRDATE = CASE.FAIRDATE
AND cashhst.[CASE] = [CASE].[CASE];

John W. Vinson [MVP]

 

[Guest]

Thanks John and Karl ... something so simple and it worked wonderfully.
Appreciate the help

I'm getting the same case number for each booth, so it's giving me over 3
million records. Is there anyway to write in the SQL, to match case and
fairdate and give me a booth number?

Join in the grid by both fields, or equivalently

FROM cashhst INNER JOIN [CASE] ON cashhst.FAIRDATE = CASE.FAIRDATE
AND cashhst.[CASE] = [CASE].[CASE];

John W. Vinson [MVP]