Skip to main content

Posts

Showing posts from June, 2011

Access conversion NOTE1: default DateSerial in VBA/Access to SQL

I have started slowly converting my Access database to SQL. The reason is that I would like to take the application online.

After trying to use Access's (version 2007) transfer tool, which did not work that well. I found Microsoft's SQL Migration Assistant 2008 for Access, nicknamed SSMA. Which you can download here.

When you install there is a niggle about the license, which you need to download into their specified directory and it must be the name they provide.

So about the create table. My one table would not convert and I could not see why. After looking at the SQL command I saw the problem was the use of DateSerial. I googled a few sites and could not find an answer

The CREATE TABLE line was:


[RequireUntilDate] datetime2(0) DEFAULT DateSerial(1980,1,1) NOT NULL,



I clicked on the whole command and copied it and then in SQL Server Management Studio, I tried to create the table and found that if I specified the date in US date format it worked:
[RequireUntilDate] datetime2(0) …

Pivot table / cross tab query or transform in SQL and Access

I have been working with and orders database and trying to move away from pivot table forms, since they are a little messy, so started playing with the SQL Transform command, which is how pivot tables are implemented in SQL OR perhaps where the original Pivot tables.
The problem I had is the Access only lets you do a Crosstab query with the query wizard on a table or query, and since I was trying to have a faster result I wanted to do just use one query. So I used the main table to create the query (Access automatically displays relation data, so on forms this would be enough) and then one by one I replace the fields with the relational fields I wanted.
So my original query looked like this

TRANSFORM Sum(OrdersTbl.QuantityOrdered) AS SumOfQuantityOrdered
SELECT OrdersTbl.RoastDate, OrdersTbl.CompanyID AS DeliverTo, OrdersTbl.DeliveryById, OrdersTbl.RequiredByDate AS DeliveryDate
FROM OrdersTbl
WHERE (OrdersTbl.Done=False)
GROUP BY OrdersTbl.RoastDate, OrdersTbl.CompanyID, OrdersTbl.DeliveryB…

The not so Good food and Wine Show..

I have not gone to the Good Food & Wine Show, for fours years. For many reasons to name a few:
The entrance does not include parking, and is mad in price even if you convert to dollars, it is ten dollars to get in. What do you get for that? NOTHINGThe people on the show have to fork out a small fortune for the having a stand, and this means that fashion food is dominant, because fashion food is more profitable, since more money is spent on brand than product.I am not a huge fan of crowds, studies show over and over again that people in crowds are not people just body moving toward the exitMost exhibitors that make it are only there to supplement their super market mentality. Brand and sales are what drive them there.So why did I go? Well the main reason I went was because I wanted to see Heston Blumenthal, whose programs I have YouTubed and seen on DSTV. Both in search of Perfection, and his Feast series which are mind boggling to say the least. So off we went, decided to get a baby…