Joining Two Queries to create a graph

[RSTechSupport]

Scenario I need help with:

Two salesman John and Joe. Both enter sales into a front end access form linked to a back end table.

Two queries one tracking Joe one tracking John's sales by current day, by time of day.

I thought, mistakenly, that if I then build a third query I would get column 1 with John column 2 with Joe that I could then build a graph off like a stock graph showing sales volume by each, two lines on one graph, charted by sales time of day. As those of you who are sharper than I have guessed already, the query result I got was cartesian product.

Sooo, what is the best query design to get the result I want. Two lines tracking two salesman on one chart by total sales volume by time of day in one hour increments. Data is recorded in one table by Salesman and time of sale (plus a bunch of other fields not relevant to this query)

 

[PHV]

Can you please post the SQL code for one query (say Joe's tracking)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RSTechSupport]

SELECT tblStats.Stats_Date, tblStats.Stats_Stamp_Time, tblStats.Stats_Via
FROM tblStats
WHERE (((tblStats.Stats_Date)=Date()-0) AND ((tblStats.Stats_Via)="L"));


Where Stats is the table where the sales are recorded, Date and time obvious, where tblStats.Stats_Via is the name of the sales person (in this case designated by code L).

Another query identical except ((tblStats.Stats_Via)="M"));

Thanks for any help on this

 

[PHV]

Something like this ?
SELECT Stats_Date, IIf(Stats_Via='L',Stats_Stamp_Time,Null) As L_Stamp_Time, IIf(Stats_Via='M',Stats_Stamp_Time,Null) As M_Stamp_Time
FROM tblStats
WHERE Stats_Date=Date()-0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit to soon.
SELECT Stats_Date, IIf(Stats_Via='L',Stats_Stamp_Time,Null) As L_Stamp_Time, IIf(Stats_Via='M',Stats_Stamp_Time,Null) As M_Stamp_Time
FROM tblStats
WHERE Stats_Date=Date()-0 AND Stats_Via In ('L','M');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RSTechSupport]

Thank you for this. Worked like a champ. This was an elegant solution to the problem. Thanks again