Setting up new databases

[Patrick Burnham]

I have been using alph5 4 version and just discoverd I have set up my application wrong. I have spent hours and hours trying to get the application to work for me. What I want to do is set up databases that will keep track of my buisness. I have tried every way I know how and I guess I haven't figured out the basics. I am a piano tech and have a names database, a customer database, a service database. I want to keep track of the pianos that I service every six months or whenever. One customer may have six or seven pianos and seviced at differant dates. I want the program to tell me what pianos need sericed for a certain month. I can not figure out a way to make this work. I have tried every way to link the customer and service databases together but it will not work. I have a names database and a customer database and a service database. The service database stores date tuned, return date, appt date, call back date, labor, tax, total etc. Question is how do I link the databases so that it works. Thanks Patrick

[crimmelcp]

RE: Setting up new databases

Patrick:
Can you zip up your application and post it to the board so we could see what you are doing.
I understand the customer and service table, but what is the name table.
Charlie Crimmel

[martinwcole]

RE: Setting up new databases

Sounds like you need a unique linking field - like customer number. be sure not to use an "auto increment" field if you do. There are several discussions on alternatives to guarantee a unique number.

[forskare]

RE: Setting up new databases

Patrick,

When you say "database", do you mean table? I suspect so.

Link the Customers table to the Service table by customer number. Without knowing more about the Names table, it's hard to tell how it should be used, as a parent or a child. Names could be the parent table and so could Customers. Tell us more about what the Names table is for.

If Customers is the Parent table, then Service is a child table linked 1:Many by customer ID.

This will get you started.

kenn

[Ken L Herrold]

RE: Setting up new databases

Patrick:

Here's how I would do it.

The customer would be the parent and his customer ID the linker.
1st child: linked on customer ID
this child holds his pianos; service date; and an automatically calculated recommended next service date.

2nd child: Linked on customer and a copy of the first with a filter the 'next service date'filtered with 'month' function for the current month. This child will display and list all of the items needing service for all customers during the current month but will display only those for the viewed customer.

Using this same filter you can print out a work sheet using the browse1 stand-alonefor the current month's customers and their service needs.

Your invoice routine could post the current invoice date to the 'last serviced'-as well as other information you would want on that 1st child record; recalculate the next needed; and get you ready for the next go-around.

Man, I'd like to fool with that!

Ken

[Patrick Burnham]

RE: Setting up new databases

The names table contains all personal and buisness names. When I enter new names in the names table I can choose to lable the record personal or buisness. In the customer table I use a pop-up window on last name and only the buisness names pop up. There is probably a better way to do this but Ihave very little knowledge, as you can see, about setting up an application. I set up a service table to save info on all pianos that I sevice like date tuned, return date, appt date, call back date, labor, tax, parts, total, notes, etc. I linked the service table to the customer table and and it dosn't matter which table is the primary the other table will not index or I can't get the other table to do anything. I even tried to build a new table that since a customer may have 7 pianos, to give each piano a record and tie that to the customer. I am trying to send you a zip but when I click the attach file at the bottom of my reply, it dosn't direct me to a file folder to choose what file to attach. Patrick

[crimmelcp]

RE: Setting up new databases

patrick:
when you click on the attached file check box and then send message, it will allow you to select the zip file to attach
Charlie Crimmel

[Ken L Herrold]

RE: Setting up new databases

Pat:

I fooled with it. Here's some Ideas!