Help with Access Query

[Jimfingerz]

Hi,

I'm trying to query our V7 database via access 2007. I've followed the instructions from a post a longtime ago. I get all the information needed but my short description comes out in a what looks like a chinese font, very bizarre!

Anyone have any ideas? I would also like to define some dates on this report, ie just report on the last months sales. Can anyone shed any light on how to do this?

Thanks in advance for you help. Below is the process I have followed so far....

Here is how to do it in Access
Open Access and click on Queries.
Double click on Create Query in Query in design View
Add these 2 tables to the view from the add table box:
Product
Order detail
close box
Locate the view icon on left hand side of the icon bar - it has a drop down by the side of it - select SQL View and delete whatever is there and paste the following:

SELECT Product.[Product reference], Product.[Short description], Sum(OrderDetail.QuantityOrdered) AS [Number sold], Sum(OrderDetail.Price) AS [value]
FROM Product LEFT JOIN OrderDetail ON Product.[Product reference] = OrderDetail.ProductReference
GROUP BY Product.[Product reference], Product.[Short description]
ORDER BY Product.[Product reference];

Save the query as My Sales.
Go back to Design View and select query from the menu bar
Select make table and give your table a name such as Total sales.
Save query again
Double click on the my sales query and accept the questions asked.
go to the table view and open the Total sales table
If I understand your needs correctly, you will see the product reference all of them), short description and the number of items sold including those you have sold nothing of, plus total sales value (in pence).

[TraceyG]

Hi,

I get all the information needed but my short description comes out in a what looks like a chinese font, very bizarre!

I can't recreate this but I am using Access 2000.

I would also like to define some dates on this report

I changed the query to look like this:

SELECT Product.[Product Reference], Product.[Short description], Sum(OrderDetail.QuantityOrdered) AS [Number sold], Sum(OrderDetail.Price) AS [value], Order.[Date Ordered]
FROM (Product LEFT JOIN OrderDetail ON Product.[Product Reference] = OrderDetail.ProductReference) LEFT JOIN [Order] ON OrderDetail.OrderSequenceNumber = Order.[Order Sequence Number]
GROUP BY Product.[Product Reference], Product.[Short description], Order.[Date Ordered]
HAVING (((Order.[Date Ordered]) Between [Type the beginning date:] And [Type the ending date:]))
ORDER BY Product.[Product Reference];

When it prompts for the start and end dates you have to enter them in the format of yyyy/mm/dd hh:mm (eg 2008/05/01 00:00) as this is how they are entered into the database.

I hope this helps.

[TraceyG]

Actually, the above may not be right as it puts in a separate line for each item. This is because the 'date ordered' field is different for each order because of the time that is included in this field. I'm afraid that my sql knowledge isn't good enough to figure out how to ignore that bit but maybe someone else could help with that.