Exporting data

[leehack]

* I know i am.

It's three relatively simple steps. First of all you need to export all of the products within your catalogue to a spreadsheet. So you have a list with all of your products contained. Secondly, export your sales analysis which details how many of each product has been sold.

Thirdly, get the first spreadsheet to look up it's name in the second spreadsheet and fill in the sales figure.

[ncklln]

b b but but but Actinic will only let me print the sales reports and so on, not save them as a spreadsheet!

[ncklln]

thanks for your patience btw!

[leehack]

Don't you have an export tab with the report selecting tool? I can't confirm as i don't have V6, but Jan has said that V6 has pretty much what V8 does, so it should be there somewhere.

[ncklln]

i don't, no. looks like i'll have to print it and then use an OCR program to get it back in. how ridiculous!

[leehack]

That sounds like a poor solution, there has to be something better available. I would be surprised if there is no export facility.

[gabrielcrowe]

perhaps this could help:

open your database 'ActinicCatalog.mdb'

Click Queries

Click Create Query using Wizard

in the tables/queries dropdown, select 'Product'

add these two available fields; 'Short Description' and 'nStockOnHand'
to the right column

press finish.




I have no idea how actinic derives the amount of an item sold offhand. I would use SQL do parse the orders table, and then use this data against the list of products

[Bruce]

Nick,

In v6 you can go to 'View | Business Settings | Ordering' and under 'Report Options' select 'Enable Export to Files', you will see that this creates 5 csv files. These are stored in your site1 folder by default and will be overwritten with each subsequent export.

You can select a range of orders and right click to select 'Re-export to files', this will export the data for those orders to the above mentioned files, from where you can copy them to a master spreadsheet or use in any other way.

Kind regards,

[gabrielcrowe]

i knocked this up, but its ugly.

save this to a file called 'exportsales.vbs' on your desktop. if will make a csv file at 'c:/output.csv' err, probably.

dont forget to change your db path. i have no idea if this will work for you in V6

'my shop database is located at:
mydb = "C:\Program Files\Actinic V8\Sites\Site1\ActinicCatalog.mdb"

'open file
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\output.csv", ForWriting, True)
objFile.Write """Prod Name"", ""Total Orders"""

	'Get data
	Set OBJdbConnection = CreateObject("ADODB.Connection")
	OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb
	SQLQuery = "SELECT [Short description], [Product Reference] FROM Product"
	Set Result = OBJdbConnection.Execute(SQLQuery)
	if Not Result.EOF then
	'thisdata="Short Desc" & VbTab & "Ref"
	  Do While Not Result.EOF
	  
	  thisdescription = Result("Short description")
	  if thisdescription <> "" then thisdescription = replace(thisdescription,"""","")
	
				Set OBJdbConnection2 = CreateObject("ADODB.Connection")
				OBJdbConnection2.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb
				SQLQuery2 = "SELECT [ProductReference],[QuantityOrdered] FROM [OrderDetail] WHERE [ProductReference] LIKE '" & Result("Product Reference") & "'"
				Set Result2 = OBJdbConnection2.Execute(SQLQuery2)
				thiscount="0"
				if Not Result2.EOF then
				  Do While Not Result2.EOF
					thiscount = thiscount + Result2("QuantityOrdered")
					
					Result2.MoveNext
				  Loop
				end if
				OBJdbConnection2.Close
	
		objFile.Write VbCrLf & """" & thisdescription & """,""" & thiscount & """"
		Result.MoveNext
	  Loop
	end if
	OBJdbConnection.Close

'close file
objFile.Close

msgbox("done")

[gabrielcrowe]

bear in mind that i changed this code several times. i'm always forgetting things you see. :P

also, something i realised, is that occasionally, after a lot of adding and deleting of products, the product references get mangled, leading to innacurate output from this little bit of code, so, er, i have no suggestions after that, lol. its for example only, and additonal work, dont trust its output currently.

[ncklln]

blimey!

thanks very much - i won't be near the pc till monday now, but i'll have a go then.

have a good weekend

[HowardW]

Hi Nick
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).
if this is OK close the table, make sure it is highlighted and got to file. select export, change the type of export to excel and you then have your table in excel.
Anytime you want to update it, double click on the My sales query and export the table again
Regards
Howard

[ncklln]

Howard - thanks soooooo much. i'm nearly there! the problem i have now is shown here.. http://www.pussyhomeboutique.co.uk/access/
the short description won't show up. any ideas?
thanks again!

[HowardW]

Hi Nick
Not sure how that happened.
Go to the query and select design view.
Where the expr1001 is showing, highlight it and select the down arrow and scroll down to Short description, select it, make sure the table line shows Product and save the query. It should then work
Regards
Howard

[ncklln]

well, thanks for all your help Howard, in the end i got it to work by pasting all the desciptions into that spreadsheet relative to the product code - but the figures it came up with were all completely ridiculous! it was telling me that we'd sold 120 of a product that i know we hadn't sold any of etc etc.
going to try again tomorrow.
thanks again though - it's the closest i've got!