access

If you are new here, you may want to subscribe to my RSS feed or the site newsletter. Thanks for visiting!

Someone read the post from 2 days ago and took this to a slightly further point.

How can I list all the details, and also list subtotals by a subset of the itemID, and then a grand total. They were referring specifically to a report of the data.

Here is some fake data I created to illustrate.

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
05666    $6.00
05886    $1.00

The report should look like this. (plus appropriate headers)

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
01 subtotal = $25.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
02 subtotal = $41.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
03 subtotal = $23.00
05666    $6.00
05886    $1.00
05 subtotal = $7.00

grand total = $96.00

Here are the steps needed in Microsoft Access 2000. I am not putting in every small detail, as I believe you should think about what you are doing, and copying every tiny detail from me will not help you.

• start the report wizard, giving it the name of the table you are needing
• select the itemID and cost fields
• choose itemID as a grouping field
• click the Grouping Options button and choose a Grouping interval of “2 initial letters”
• no sorting is needed, but under Summary Options you want the Sum of the cost field
• pick a style and finish the report’s creation (Note: give it a proper name, like rptCosts, or similar)

The report is not quite what we wanted, but is close.

Next I went into the design view of the wizard created report and …

• deleted the “Summary for …” item from the itemID footer
• moved the “Left$ …” item from the itemID header to the itemID footer
• rearranged the objects in the itemID footer to better emulate the design I wanted
• went into the Sorting and Grouping section under the view menu
• changed the itemID Group Header to “No”
• continued to play with formatting until I had it the way I wanted it

See printouts, before and after changes, by clicking on the links in this sentence.

The key in my MS Access self-done education period was to use the wizard like we just did, and look at the design and especially properties of objects, and the Sorting and Grouping dialogue box. I experimented and discovered “stuff”.

Hopefully I can help you discover that “stuff” faster than I did, but in a way that still helps you “see” what is going on. Don’t ever depend solely on a recipe book. Discover the principles so that you can create your own recipes.

I have finally resumed my series about using Access on a PC to feed a MySQL database on the web! Read part 2 here. This part describes the purpose of the database and describes 2 of the websites that get fed by this little homegrown system.

Remember that this method was created for websites that exist on servers that do NOT allow remote MySQL connections. Some servers do allow the remote connections, which simplifies the whole process.

There are times when I would love to enter data into an MS Access database on my local machine, and then pass that data to a MySQL database on a web site. However, firewall rules normally prevent accessing those databases from outside the network where the respective server machines are located.

How can I get around this?

In researching this question, one reasonably simple answer is to have a small slave program on the website that hosts the MySQL database. Your MS Access database then passes the data to this slave, which then adds the data to the database on the internet.

Specifically, the approach I took is this.

• enter data into MS Access database on local machine
• Access exports data to a text file (or you could get fancy and do XML output)
• Access initiates an FTP command which transfer the text file to the website
• Access initiates the slave program on the website
• slave program reads the uploaded text file and inserts data into the MySQL database
• text file is deleted from the website

The slave program is created using PHP.

Next posting will start a series that will give more details. In the meantime, you may want to play with this idea on your own and then compare with my solution later.

This posting also exists as an article on the main site. See it here.