[smalpierre]

I'm a computer programmer by education, and worked as a financial systems / web programmer for a bank as long as I could stand corporate america - so I'll weigh in and help you out here.

PM me and I'll build you an example database to get you started, send you some insert / update / select queries (deletes are way too simple), and give you some code to start with in the language of your choice.

I suggest MySql. It's free, and it's graphic interface is really nice and very powerful. You could also use Microsofts professional product Sql Server which I used to use a lot. There's a free version of it too. MySql is easier to find info on though.

Unlike Access, there will be no built in programming environment. Access's environment sucks anyway for many many many reasons. So you'll need to figure out your target. If you want to build desktop apps you have a few good choices. Qt which is C++, Lazarus which is a Delphi clone and uses Object Pascal.

C++ is a little bit daunting, but Qt makes it not so bad. I've cross compiled the same exact code to target Linux and Windows - and it looks like you can do the same for Android and IOS now too but I haven't tried it.

If you're going for a web app, PHP is where it's at. Getting a webserver set up with PHP on Windows is a pain, but you can download Wampserver and it'll pretty much do it all for you. Most everything I've coded in the last few years has been web apps in PHP / MySql.

Like I said, PM me and I can help you get rolling pretty quick.

 

[smalpierre]

The basics are a real ***** to learn, but once they click, the rest of it starts to fall into place really quickly. If you learn the right way first, it makes it a LOT easier

 

[smalpierre]

Writing code in VB for access?

Yes, most all user interface code in Access is in VB. Technically it's VBA since it's the built into application version rather than the standalone. product in Visual Studio but what's the difference ... Access is a data storage engine and user interface building platform all in one - although both are pretty bad particularly data access.

The data access is done via sql like any other database, but it's a strange version of sql that is totally not portable with other databases. I had to port an Access database to Microsoft Sql Server - I beat my head on it for a while. Cursed the programmers that built the crappy system, ended up getting authorized to demo and build from the ground up with Sql Server, and all new VB - technically ASP in VBS - since it's the version built into the crumby MS IIS webserver.

 

[smalpierre]

Driftweed - if you want the really really easy way to do what you want to do, spend $300 on Quickbooks - get the Premier Contractor edition. It'll do way more than you'll ever code yourself.

It's already got built in products, which can be assemblies made of other products and services, you can set up customers, produce estimates with the products, you can record purchases for projects, track employee time per project, run reports that show what you estimated costs to be vs what they really are ...

Basically once it's set up you can add a customer, create an estimate, track actual costs, and run reports that tell you which jobs were the most profitable very quickly without any coding. Oh, and you can see who worked on what projects too.

Unless you just want to build it - which I totally get

 

[smalpierre]

Customers table, Products table, and a join table - name it something like Invoice_Lines table. Invoices_Lines holds foreign keys for Customers and Products. Customers and Products each have a primary key named the same as the foreign key for them that is in the Invoices_Lines table. Then go to the graph screen and drag from Customers primary key to the same name in Invoices_Lines. Then drag from Products primary key to the foreign key in Invoices_Lines named the same. That forms the basis for your relationship. You put all the Customer name, address, zip code, phone and so forth fields in Customers table, and the names for your finished 'PRODUCTS' in the products table. Do your reports based on Invoice_Lines table.

Use autoincrement fields for the primary keys. Saves a lot of problems later. If you use customer name and you try to insert a second "john smith" it will fail. Also when you're doing lookups in a many to many table it's a lot less work for the computer to compare than text string comparison - among other issues it solves.

You COULD use a composite key, but that introduces it's own problems and doesn't 100% solve the original one.

Also you want to break customer contact info out into it's own table so you can store multiple phone numbers for a single contact for example. You COULD have two phone number fields but what if a customer has 3? You could put 3 in there - but then for each customer you're storing the data space for 3 phone numbers when most only have 1 ...

This falls under database normalization which is a technique to prevent data anomalies. Sometimes though, it can be overdone to the point of extreme complexity when it should be a tool to make it more simple.

Assume you have customers which can be consumers or businesses:

http://dba.stackexchange.com/questions/41427/database-design-people-and-organisations

 

[smalpierre]

This is kind of what you want. Extremely oversimplified and limiting in a lot of ways, but it's a start.

I'll post the SQL create script (from MySql) and give you some sql to start with - how to insert, update, delete, and most importantly select and filter the data in SQL. You'll have to modify it probably for Access.

 

[smalpierre]

Full working MySql create script:

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';



CREATE TABLE IF NOT EXISTS `test`.`company` (

`CompanyId` INT(11) NOT NULL AUTO_INCREMENT ,

`CompanyName` VARCHAR(45) NOT NULL ,

`Street` VARCHAR(45) NOT NULL ,

`City` VARCHAR(45) NOT NULL ,

`State` VARCHAR(2) NOT NULL ,

`Zip` INT(5) NOT NULL ,

PRIMARY KEY (`CompanyId`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS `test`.`company_has_contacts` (

`Company_CompanyId` INT(11) NOT NULL ,

`Contacts_ContactId` INT(11) NOT NULL ,

PRIMARY KEY (`Company_CompanyId`, `Contacts_ContactId`) ,

INDEX `fk_Company_has_Contacts_Contacts1_idx` (`Contacts_ContactId` ASC) ,

INDEX `fk_Company_has_Contacts_Company_idx` (`Company_CompanyId` ASC) ,

CONSTRAINT `fk_Company_has_Contacts_Company`

FOREIGN KEY (`Company_CompanyId` )

REFERENCES `test`.`company` (`CompanyId` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_Company_has_Contacts_Contacts1`

FOREIGN KEY (`Contacts_ContactId` )

REFERENCES `test`.`contacts` (`ContactId` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS `test`.`contacts` (

`ContactId` INT(11) NOT NULL AUTO_INCREMENT ,

`Position` VARCHAR(45) NOT NULL ,

`FirstName` VARCHAR(45) NOT NULL ,

`LastName` VARCHAR(45) NOT NULL ,

`OfficePhone` VARCHAR(45) NULL DEFAULT NULL ,

`CellPhone` VARCHAR(45) NULL DEFAULT NULL ,

`Email` VARCHAR(45) NULL DEFAULT NULL ,

PRIMARY KEY (`ContactId`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS `test`.`floorplans` (

`FloorplanId` INT(11) NOT NULL AUTO_INCREMENT ,

`CompanyId` INT(11) NOT NULL ,

`UnitName` VARCHAR(45) NULL DEFAULT NULL ,

`SquareFeet` INT(11) NOT NULL ,

`Bedrooms` INT(11) NOT NULL ,

`ListPrice` DECIMAL(7,2) NOT NULL ,

`Description` TEXT NOT NULL ,

PRIMARY KEY (`FloorplanId`, `CompanyId`) ,

INDEX `fk_Floorplans_Company1_idx` (`CompanyId` ASC) ,

CONSTRAINT `fk_Floorplans_Company1`

FOREIGN KEY (`CompanyId` )

REFERENCES `test`.`company` (`CompanyId` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS `test`.`installers` (

`InstallerId` INT(11) NOT NULL AUTO_INCREMENT ,

`InstallerName` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`InstallerId`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;



CREATE TABLE IF NOT EXISTS `test`.`salesorders` (

`SalesOrderId` INT(11) NOT NULL AUTO_INCREMENT ,

`FloorplanId` INT(11) NOT NULL ,

`InstallAddress` VARCHAR(200) NOT NULL ,

`SalePrice` DECIMAL(7,2) NOT NULL ,

`InstallerId` INT(11) NULL DEFAULT NULL ,

`OrderDate` DATETIME NOT NULL ,

`InstallDateReq` DATETIME NOT NULL ,

`InstallComplete` DATETIME NULL DEFAULT NULL ,

`Paid` TINYINT(1) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`SalesOrderId`, `FloorplanId`) ,

INDEX `fk_SalesOrders_Floorplans1_idx` (`FloorplanId` ASC) ,

INDEX `fk_SalesOrders_Installers1_idx` (`InstallerId` ASC) ,

CONSTRAINT `fk_SalesOrders_Floorplans1`

FOREIGN KEY (`FloorplanId` )

REFERENCES `test`.`floorplans` (`FloorplanId` )

ON DELETE NO ACTION

ON UPDATE NO ACTION,

CONSTRAINT `fk_SalesOrders_Installers1`

FOREIGN KEY (`InstallerId` )

REFERENCES `test`.`installers` (`InstallerId` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;





SET [email protected]_SQL_MODE;

SET [email protected]_FOREIGN_KEY_CHECKS;

SET [email protected]_UNIQUE_CHECKS;

 

[smalpierre]

I have quickbooks pro as well.

I didnt find what I wanted in it. I service apartment complexes. So each customer will have a unique set of services used repeatedly. For example: Customer one will have riughly 12 products, invoiced multiple times over the year.

Couple that with an all new service I am starting next month, where I will have tech's doing work, and well...

I am paperless. I want my tech to log in, record jobsite, hours, service performed, generate an invoice, as well as an inventory restocking list.

You can do it in quickbooks, not exactly sure how you'd go about setting up customer specific products but I'm sure you can.

The premeir contractor version has all the cool reports and all that. I think in pro you'd have to build your own. Anyway - when I get back from my day job, I'll trim down that create database sql so it'll be more portable, and whip out a few queries for ya.

 

[smalpierre]

There's a ton of them out there, MySql being the most prolific. I've used everything from Access to Oracle, and MySql is my favorite by a long shot - although I'm not a fan at all of the MyISAM table type that most MySql databases are made with.

Mostly people use MyISAM because at one time it was a lot faster. The main problem I have with it is the lack of foreign keys - you cannot link tables together so your front end code has to handle data integrity.

It's not really that much faster anymore. If you're building a website with millions of requests / day it's still not really that much more efficient. I think it's silly to give up one of the cornerstones of a relational database for a marginal speed increase, ESPECIALLY when I see horrible front end code that's slow as molasses, and written with Java or some other resource hog like Ruby on Rails, or some bloated PHP framework.

 

[smalpierre]

I just looked at MySql, $2,000 for one year of use? I would find a different way to track who painted a house. Maybe stick with Access or even Excel might have a function to do that.

That's mostly for things like tech support which a lot of organizations seem to think are necessary in case the developers run into problems that they don't know how to resolve.

For most issues, internet discussion boards are more than adequate - but if you're a bank, and there's billions of dollars worth of transactions crossing the database - it's a bit of added insurance that problems can and will be dealt with.

 

[smalpierre]

Ok, I trimmed down the create script since the generated scripts are extremely verbose, and use database specific commands.

Other than maybe having to edit the data types or something (on Sql Server datetime would become date if I remember correctly) this should be pretty close to working on most databases.

It definitely works on MySql - just tested it. All the verbose stuff guarantees that when you run the script again on another MySql database server, it will use the same table type, and database name as it was originally. In this case it's easier for you to let the system defaults work their magic.

CREATE TABLE company (
CompanyId INT(11) NOT NULL AUTO_INCREMENT ,
CompanyName VARCHAR(45) NOT NULL ,
Street VARCHAR(45) NOT NULL ,
City VARCHAR(45) NOT NULL ,
State VARCHAR(2) NOT NULL ,
Zip INT(5) NOT NULL ,
PRIMARY KEY (CompanyId)
);

CREATE TABLE contacts (
ContactId INT(11) NOT NULL AUTO_INCREMENT ,
Position VARCHAR(45) NOT NULL ,
FirstName VARCHAR(45) NOT NULL ,
LastName VARCHAR(45) NOT NULL ,
OfficePhone VARCHAR(45) NULL DEFAULT NULL ,
CellPhone VARCHAR(45) NULL DEFAULT NULL ,
Email VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY KEY (ContactId)
);

CREATE TABLE company_contacts (
CompanyId INT(11) NOT NULL ,
ContactId INT(11) NOT NULL ,
PRIMARY KEY (CompanyId, ContactId) ,
INDEX fk_Company_has_Contacts_Contacts1_idx (ContactId ASC) ,
INDEX fk_Company_has_Contacts_Company_idx (CompanyId ASC) ,
CONSTRAINT fk_Company_has_Contacts_Company
FOREIGN KEY (CompanyId)
REFERENCES company (CompanyId)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_Company_has_Contacts_Contacts1
FOREIGN KEY (ContactId)
REFERENCES contacts (ContactId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

CREATE TABLE floorplans (
FloorplanId INT(11) NOT NULL AUTO_INCREMENT ,
CompanyId INT(11) NOT NULL ,
UnitName VARCHAR(45) NULL DEFAULT NULL ,
SquareFeet INT(11) NOT NULL ,
Bedrooms INT(11) NOT NULL ,
ListPrice DECIMAL(7,2) NOT NULL ,
Description TEXT NOT NULL ,
PRIMARY KEY (FloorplanId) ,
INDEX fk_Floorplans_Company1_idx (CompanyId ASC) ,
CONSTRAINT fk_Floorplans_Company1
FOREIGN KEY (CompanyId)
REFERENCES company (CompanyId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

CREATE TABLE installers (
InstallerId INT(11) NOT NULL AUTO_INCREMENT ,
InstallerName VARCHAR(45) NOT NULL ,
PRIMARY KEY (InstallerId)
);

CREATE TABLE salesorders (
SalesOrderId INT(11) NOT NULL AUTO_INCREMENT ,
FloorplanId INT(11) NOT NULL ,
InstallAddress VARCHAR(200) NOT NULL ,
SalePrice DECIMAL(7,2) NOT NULL ,
InstallerId INT(11) NULL DEFAULT NULL ,
OrderDate DATETIME NOT NULL ,
InstallDateReq DATETIME NOT NULL ,
InstallComplete DATETIME NULL DEFAULT NULL ,
Paid TINYINT(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (SalesOrderId) ,
INDEX fk_SalesOrders_Floorplans1_idx (FloorplanId ASC) ,
INDEX fk_SalesOrders_Installers1_idx (InstallerId ASC) ,
CONSTRAINT fk_SalesOrders_Floorplans1
FOREIGN KEY (FloorplanId)
REFERENCES floorplans (FloorplanId)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_SalesOrders_Installers1
FOREIGN KEY (InstallerId)
REFERENCES installers (InstallerId)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

 

[smalpierre]

You will need to create a database for this to run against, then set the interpreter to run commands on that database. You do that like this:

create database MyCompany;
use MyCompany;

 

[smalpierre]

Updated EER diagram

EDIT again ...: Writing the insert queries I noticed that I didn't change some things that I normally do differently from the way the MySql code generator does.

Now I remember why I don't use identifying relationships ...

Ill see if I can update the image to reflect so I don't have to add another one ...

 

[smalpierre]

Here's the inserts to insert data for each table.

Note: there are fields that require data from other tables to be there, or you'll get errors when you try to insert them.

insert into contacts (
Position,
FirstName,
LastName,
OfficePhone,
CellPhone,
Email
)
values (
'Apartment Manager',
'Jane',
'Smith',
'555 555 5555',
'555 111 1111',
'[email protected]'
);

insert into company (
CompanyName,
Street,
City,
State,
Zip
)
values (
'Slum Villas',
'5025 Ghetto Lane',
'Detroit',
'MI',
'54666'
);

insert into company_contacts (CompanyId,ContactId) values (1,1);

insert into floorplans (
CompanyId,
UnitName,
SquareFeet,
Bedrooms,
ListPrice,
Description
)
values (
1,
'The Junkworth',
350,
1,
325.00,
'walls, promar 200 flat color code 0-23-1248. Trim painted with wall paint, same color. Ceilings: ceiling white. Countertop refinish color: almond.'
);

insert into floorplans (
CompanyId,
UnitName,
SquareFeet,
Bedrooms,
ListPrice,
Description
)
values (
1,
'The Methanado',
550,
2,
525.00,
'walls, promar 200 flat color code 5693458. Trim painted with chemical resistant epoxy in almond. Ceilings: ceiling white. Countertop refinish color: bright white.'
);

insert into installers (InstallerName) values ('DriftWeed');

insert into salesorders (
FloorplanId,
InstallAddress,
SalePrice,
InstallerId,
OrderDate,
InstallDateReq
)
values (
1,
'3250 White Horse Drive, Detroit, MI 35402',
325.00,
1,
now(),
now()
);

 

[smalpierre]

One thing you wanted to do is to pull a list of jobs that a particular installer worked on.

If you wanted to find out every job that Driftweed has ever done you could do something like this:

select * from salesorders where InstallerId = 1

the * means "all fields". It's not good form at all when you're building the front end, but for now it'll do.

This will produce 1 record since we only have 1 sales order.

When I inserted the record, I used "now()" for the datetime fields to make it easy on me so I could focus on the big picture. The installdatereq field is when they want the job done - like next tuesday. "now()" as you can imagine is just the current time on the computers internal clock.

One more thing - the "InstallComplete" field has no value, and is allowed to be null. When the installer finishes the job, you enter a date.

So right now, the above query lists all jobs the installer has ever done, AND jobs currently assigned.

To filter out jobs that are currently assigned, and ONLY get jobs that are complete you use the "where" clause of the select query.

select * from salesorders where InstallerId = 1 and InstallComplete is not null

At this point it won't return any rows since there are no sales records that match that criteria.

 

[smalpierre]

Am I on the right track so far?

What platform do you want to run this on - desktop, web app, or mobile? Or as a combination of any / all of the above?

 

[smalpierre]

Also - if you want to pull all work orders an installer has assigned but are not complete you'd do this:

select * from salesorders where InstallerId = 1 and InstallComplete is null

Oh, and you'll probably want to order the list to show the records in some order - like ordered by the date completed, newest first.

To do that, you'd do this:

select * from salesorders where InstallerId = 1 and InstallComplete is not null order by InstallComplete desc

If that returns them in the wrong order, change desc to asc. they stand for ascending, and descending.

 

[smalpierre]

Python

Python is a scripting language, not a platform

Why Python? I know some people that like it. I tried it years ago but quickly determined that it just wasn't what I wanted.

Desktop apps I do in C++ or ObjectivePascal. Web apps, it's PHP all the way. Well, I've seen some really bad ass web apps built with Pascal running as an ISAPI (or NSAPI) on the webserver.

If you're looking for the most blazing speed possible in a webapp, that's the fastest by a long shot - all compiled code, no interpreter to slow things down.

They can be made in C/C++ as well.

 

[smalpierre]

Ok, I had a cancellation - so I figured I'd play with this for a minute.

What you might be wondering is how do you pull a record set where each line contains data from multiple tables? That's easy - you use join syntax in the sql.

At this point, you cannot use * to select all fields, if you want them all you have to explicitly declare them all. Usually this isn't the case, and you'll only want to pull data that you need on that report.

Here's the query - I'll post again with a photo of my sql console with the expected outputted data set. There isn't a full set of test data, so it'll only display 1 row. Even on simple queries like this one you will want a good data set with multiple scenarios to run the query against to make SURE that you're filtering what you want to get rid of, and not filtering anything you want to keep.

EDIT: Formatting with tabs and spaces is stripped by the forum editor. I'll leave this so you can copy / paste, refer to the next post's photo for how I actually format this.

select salesorders.InstallAddress,
salesorders.SalePrice,
salesorders.OrderDate,
salesorders.InstallComplete,
floorplans.UnitName,
floorplans.Description,
installers.InstallerName
from installers
inner join salesorders on salesorders.InstallerId = installers.InstallerId
inner join floorplans on floorplans.FloorplanId = salesorders.FloorplanId
where installers.installerId = 1
and salesorders.InstallComplete is null;

This select pulls an installer, joins sales orders that he is attached to, then joins floorplans table to retrieve more data. Since there are multiple tables, and the fields may have the same name (like InstallerId is in multiple tables) so you use tablename.fieldname to pick a field from whichever table you want. The rest works the same.

White space in sql does not matter - the spaces and tabs. Only commas, and semicolons denote separation of fields, and end of command btw - figured I'd mention that since it's no longer a single line query.

 

[smalpierre]

Oh forgot to mention - that query is for sales orders that are assigned to Driftweed and not yet completed - hence the InstallComplete is null.

But we're not done yet - you might want to add contact data for the apartment manager, or maintenance supervisor so you know who to call about it.

Later in the report, if you select the sales prices you can have your report add up the gross reciepts for that installer too. Starts to get much more complex very quickly if you want to do profitability by job or by year reports, but it's a start. That's all done by the front end though. All the database does is store and retrieve data - well you CAN make the database do it, but you can hammer screws into drywall too

Here's a screen cap of my sql console with the outputted data: