Microsoft access 2010

9753 Views 104 Replies 10 Participants Last post by smalpierre, Jun 8, 2014

Driftweed

Discussion Starter · Apr 6, 2014

Finally broke down & got a laptop...

Anyone got any pointers for this program? Seems like it has potential to link customers to jobs & jobs to customer specific "products"

Example: I bid an apartment complex interior repaint based on various floor plans. So each customer has roughly 4-8 specific "products" where the price is unique to that customer.

I would also like to link to what person worked on that individual product. This way I can click on the employees name & see a history of all jobs he ever did.

Save

1 - 13 of 105 Posts

macmikeman

· #2 · Apr 6, 2014

I don't use Access, but have certification for Filemaker 10,11,and 8.5
YouTube is going to be your friend in this subject.
They are pretty similar programs, with drag to set relationships.
When it comes to relational databases the sky is the limit, you can do pretty much whatever comes to your mind.

Quickbooks is a good example of what I am talking about.

Save

macmikeman

· #3 · Apr 6, 2014

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.

Save

macmikeman

· #4 · Apr 6, 2014

See less See more

Save

macmikeman

· #16 · Apr 30, 2014

Filemaker rocks. No two ways about it. But it takes learning to produce good output. Same goes for photoshop. Or Garage Band. You get back out what you put in. My experience is that construction related fields workers and owners are usually way too lazy and also pretty much cheap skates looking for free or next to free when it comes to software and frankly are not a good market to sell to.

Save

macmikeman

· #27 · May 11, 2014

smalpierre said:
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

I always use an autoincrementing number field that does not allow any user to input for my Primary fields. I also put timestamp, user, user modification stamp fields into every table, but that is getting much deeper than what I felt was a basic explanation of setting up tables and relationships. Filemaker is truly way more flexible than all other database creation programming interfaces, - primary keys are actually not required, it will still work fine and perform as a relational database without them, but just about nobody I know ever built a solution that didn't utilize primary and foreign keys or follow data normalization rules.

Save

macmikeman

· #32 · May 11, 2014

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.

See less See more

Save

macmikeman

· #59 · May 13, 2014

Well if this circle jerk hasn't chased off the OP by now, nothing will.......

Save

macmikeman

· #61 · May 13, 2014

Driftweed said:
Nope still here...laptop been in the shop...should get it back tomorow.

Buncha nerds... Carry on

So then have we agreed we are going to use mysql over access? Or both?

Well if you want to have to construct continual query statements for each item you wish to know then use the SQL approach. If you want to know the same exact information in a timely fashion then use scripted buttons and fields in a format such as Access or Filemaker and let that do the work for you. I would youtube or google the products if I were you and watch a video or two to see which method is actually easier or if you are possibly getting led down a path you never intended by strangers who are impressing you with their mighty knowledge on coding and deployment for their own amusement and glory. The other good thing mentioned was Quickbooks contractor version- A great resource right off the shelf.

Save

macmikeman

· #70 · May 16, 2014

I don't think I ever said Quickbooks was created with Access. I believe I stated it used SQL, but if I am wrong about that I stand corrected.

Mr SmallPierre, I salute your experience and training. I however was , like you trying to help a fellow tradesman on his way along a path to a solution, but unlike you, I didn't try to lead him to the one that requires 5 yrs of post secondary education along with ten years or whatever it was working for Oracle or the like.

I am not a Filemaker Salesman either, but I do have years of use of it and find it to be a very full featured database system regardless of your dismissal of it.

As to the statements of how well it works with web or on Ipads, well let me help you with that. Here is an introductory video on that very subject. You should watch before you make statements on how functional the product is. I think you have no idea. By the way, it is very easy to add tables, adjust the schema on the fly, add fields, reports, summary's, produce layouts, bring in data from other tables - inside and outside joins , run in data from tables related thru thirty other ones if you need to. Look for yourself http://www.filemaker.com/products/overview.html?video=fmpl-overview

See less See more

Save

macmikeman

· #71 · May 16, 2014

Oh, I forgot, there is plugins for Quickbooks.

Save

macmikeman

· #76 · May 16, 2014

Ok , I herby drop my defensiveness. Ok , so you looked at it and see what I see in it. IT'S EASY. Enough about it. My actual real love is something entirely different, and also easy to get the hang of because it uses the original hypercard language as the basis of it's IDE. It is open source and free of charge if you are not pushing an enterprise solution. Currently goes by the name LiveCode. Scottish guy buys the rights to the original Hypercard from Steve Jobs after Jobs banishes it from the Apple OS. He turned it into a multiplatform IDE that works for Windows, Linux, MAC OS, Iphone, Ipad, Android, and possibly even for I think Blackberry. Hypertalk is pretty close to Python in ease of scripting, but this also is drag and drop gui. Supports MySql, SqlLite, Oracle, Valentina, and ODBC and XML.

So anybody reading this if you want to develop your own apps in a really easy to learn English language coding IDE , one that does not require you to memorize or utilize a ton of symbols then have a look at LiveCode. I did mention free right? Since you have to script all your routines such as printing it is not going to be anywhere near as easy as let's say .. Oh I dunno , , Filemaker, but it is FREE. Here is your link- http://LiveCode.com/

See less See more

Save

Reactions: 1

macmikeman

· #79 · May 16, 2014

Here are some tips. Every object is scriptable. You don't have to declare variables if you like, but good coding practices and all..... Every object has properties, and you can use that to do some amazing things- have a graphic object for instance contain nested arrays inside its custom properties. Or large containers of text or graphics or video can be stored inside any objects custom properties. And everything you put on the cards (screen) is an object. There are no classes to deal with. I don't have any formal training , but I learned Hypertalk just by reading all thru the user manual that comes built in with the program. That was something like 25 or so years ago now, and they have put a real polish on the original Hypercard. You are right, you still have to learn the syntax, but it is pretty much just like the English language. My best way to describe it is to say it is ''event'' driven programming. And all the events are english.

Save

macmikeman

· #81 · May 16, 2014

So, for fun I can do some things no real coder would ever do.

On mouseup put ''10 inches'' into Sally.

Sally just became a local variable that contains the text 10 inches. I didn't have to declare Sally a variable. how simpler can it get? Lots.

Put ''BILLY'' into fld ''Agness''. Now drag out a field from the tools box and drop it onto the card canvass. Click on it and an inspector turns up , where you can name the field Agness. BILLY is now inside Agness.

I have a sick and twisted mind, at least that is what my Mom used to say........

See less See more

Save

1 - 13 of 105 Posts

This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.

Top Contributors this Month