Microsoft access 2010

macmikeman said:

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.

Click to expand...

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

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.

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;

SQL of one form or another sits at the back of almost all relational databases. There are numerous versions. SQLITE is another free open source version besides the open source MYSQL. The thing is that with the bulk of them you still have to create a front end gui to display the data in a presentable form, and also you need to create reports and setup for printing. Programs like Access and Filemaker do all the front end lifting for you if a basic looking front end is all you want. Both have capability to extend that far beyond the simple, just as how all the spreadsheet programs will allow advanced formulae to be utilized in making a real deal spreadsheet program. Relational Databases kill the hell out of spreadsheets in usability and reporting however. And when done right there is only one place to enter data, and no repeating of any input. For simplicity though, you cannot beat Filemaker. An independent but fully owned by Apple company. What is best about it is you can create a full database applications for use on Ipads and Iphone without having to run your app thru approval and paying Apple 1/3 of selling price at the Apple Store when you market them. Anybody who has put an app thru the app store process knows what a trial that can be. I can sell apps (albeit without the exposure that the app store can provide..) at the price I decide, and the content I choose.
That is the best part about it.

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
);

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 ...

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()
);

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.