SQL Report for Generating Labels

[bfcoop]

Our food coop buying club has used MS Access for years to process orders to local sources. Members email their orders to a designated "batcher" who then enters the orders into an Access DB on her PC. I'm currently in process of converting that primitive arrangement to Foodclub. The Access DB has various forms and reports that were created at least 20 years ago. Most of them can be replaced by Foodclub functions, but one Access report generates specially formatted order info that we print out on sheets of 4" X 1" sticky labels that we then apply to products as we bag them at our distributions. Each label shows Member Name, Product Code, Short Description, Quantity, Unit Price, Total Price (i.e. Quantity X Unit Price), Date, and Manufacturer -- and again, all this info has to fit into a 4" X 1" space. The Foodclub system does not seem to have any reports like this. I thought I might be able to extract the source code from the Access report and use it to create SQL that will accomplish the same function, but Access refuses to show me any usable source code. My question: Does anyone out there have any label-generating SQL that I can "borrow" and then adjust to our needs? The way things are looking right now, I may have to resort to exporting the orders from Foodclub to Access each month, then generating the labels from Access. But I'd really love to get rid of that Access "system" altogether!

[support]

You're right, there is nothing "out of the box" in Foodclub that will print 4x1" labels. But a page could be restyled using CSS to fit, if you find a page with all the data you need. From the info you listed, it sounds to me like Invoices would be the only page that has all of that info for each item. You could try using CSS to restyle Invoices, based on some example code you can find in CSS Bag o'tricks and other sections on the FAQ. I can help if you get stuck.

SQL is a possibility, but I'd have to add some custom views so you can actually see the order data. All you have access to by default is your product database, which does not have who ordered what. But even with the custom views, I think an SQL query would be more difficult than restyling Invoices, if its even possible, mainly because of how actual prices for invoices are stored. If you didn't care about actual prices, and just took the order price (from product database at time of order), an SQL query would be doable, but still more difficult than CSS restyling.

[bfcoop]

On further reflection, I realized there was nothing in the system (well, nothing to which I actually have access) that I could use SQL on to generate these labels. Unfortunately, I'm not familiar with CSS, but it certainly sounds like the way to go on this. Guess it's time to take a "crash course" on CSS -- and to check out the bag o' tricks. Thanks for the helpful suggestion!