I have a table with the following fields.(Ticketnum,Date,Time,Day,Weight,Lot,Product s,Description,Rail,Operator). I am trying to create a query in Access 2016 to pass to a recordset in a vb6 application. I will be using the data in a report. I need everything out of the table (SELECT *) and also the sum of the weight column grouped by the rail column where the date field = a date that will be passed to the query from the vb6 app. I have created two separate queries at the moment. Each one will run independently from each other. One Selects * where date = the date that is passed. I get good data.
SELECT ScaleData.*
FROM ScaleData
WHERE (((ScaleData.Date)=[prmDate]));
The second will give me the total weights for the weight field grouped by the rail field.
SELECT Sum(ScaleData.Weight) AS ['Total'], ScaleData.[Rail]FROM ScaleData
WHERE (((ScaleData.[Date])=[prmDate]))
GROUP BY ScaleData.[Rail];
When I create a query with both of the above queries and run it I get duplicate rows. I do not get duplicate rows when I run them independent of each other.
SELECT DISTINCTROW Select_Data.*, Select_Rail_Totals.*
FROM Select_Data, Select_Rail_Totals;
Can anyone help me with getting the data I need without duplicate rows? Any help would be appreciated.
Thanks,