Alpha Video Training
Results 1 to 6 of 6

Thread: Many tables into one

  1. #1
    MIKE LYON
    Guest

    Default Many tables into one

    Hi guys,

    I am trying to set up a structure that allows an unspecified amount of tables to merge into a single table/set.

    I.E.

    I have a dbf to run a quote system on which I am adding a stock element to which is looked up from an imported catalogue table.
    The idea is to be able to add another catalogue at any time.
    As the catalogues each are fairly large my idea was to make each catalogue a seperate table and each seperated on a seperate tab on the input page.

    This is fine until I come to joining them up.
    Each quote may contain some items from catalogue 1 and some from cat 2 etc but when it comes to the reports etc they need to be shown as one quote (fields will be similar).

    Any ideas how I would structure this or join them bearing in mind that I may need to add a catalogue at any time.

    Many thanks for your help.

    Mike

  2. #2
    "Certified" Alphaholic forskare's Avatar
    Real Name
    Ken Nordin
    Join Date
    Apr 2000
    Location
    Walker, MN
    Posts
    7,545

    Default RE: Many tables into one

    Mike,

    Can you do it this way? Use one table for all catalogs and have a field called, 'catalog' where you would list the various catalogs.

    Or, You could have a parent table for the catalogs and catalog company info and a child table for catalog item linked to each catalog by catalog ID.

    Reverse the set for creating reports, etc.

    kenn

  3. #3
    MIKE LYON
    Guest

    Default RE: Many tables into one

    Hi Ken,

    Thanks for your speedy reply.

    A single table setup would be great to work with keeps things simple and easy to sort but the only thing I am wary of with this approach is that the catalogues are large one is 100,000 items alone. So when they select an item via a lookup in a browse it takes a while to search each time. So with numerous catalogues added on the user would soon get fed up with waiting in a real time enviroment.

    The browse has two lookups (one or the other can be used)
    1. enter code direct to pick up product
    2. enter part of description to show all products with same.

    the second is done via a field rule lookup with a filter script to take out any products not same as user entered.

    Is this the quickest way to search if so then it is way to slow with one catalogue in let alone more than one?

    It goes through the whole table twice before returning a list.

    Any thoughts?

    Mike

  4. #4
    "Certified" Alphaholic forskare's Avatar
    Real Name
    Ken Nordin
    Join Date
    Apr 2000
    Location
    Walker, MN
    Posts
    7,545

    Default RE: Many tables into one

    Mike,

    There are several who are more versed in this than I. Have you looked at Lightening Query Optimization? The following is what the A5 Help as to say.

    Lightning Query Optimization is a feature that Alpha Five automatically activates to increase the speed at which queries are run. By using information contained in the indexes you have created, Alpha Five can dramatically speed up queries.

    To take advantage of this time-saving feature, both the query you design and the pre-existing indexes must be compatible with Lightning Query Optimization.

    I haven't personally used it but many have met with good success. It would be worth your while to look into this. There is a chapter in the manual about it.

    For a search to go through the table twice before returning an answer is not good. How can you tell that it is searching the table twce?

    kenn

  5. #5
    "Certified" Alphaholic forskare's Avatar
    Real Name
    Ken Nordin
    Join Date
    Apr 2000
    Location
    Walker, MN
    Posts
    7,545

    Default RE: Many tables into one

    You might want to download Bill's zip file to view what he's doing with LQO. It may give you some ideas. Bill's pretty sharp on this subject.

    FWIW, you don't have to click the "email their reply to me" box more than once.

    kenn

  6. #6
    MIKE LYON
    Guest

    Default RE: Many tables into one

    Ken,

    When the lookup runs it shows % searching in blue at bottom of form and number of records - this goes to 100% twice.

    It may be that the filter I have put into the lookup is at fault.

    containsi(Code+Description+Range,"*"+trim(var->lookup)+"*") .AND. PRICE0.00

    With the displaying order as - RANGE+CODE+DESCRIPTION

    This allows the user to enter anything they want and will pick up all records containing this in the range, code and description.

    I will take a look at Bills LQO and let you know if it helps

    Thanks again.

    Mike

Similar Threads

  1. No of tables
    By Robin Smith in forum Alpha Five Version 5
    Replies: 4
    Last Post: 04-21-2005, 05:22 PM
  2. Tables With Two IDs
    By George Smyth in forum Alpha Five Version 6
    Replies: 2
    Last Post: 01-13-2005, 12:00 PM
  3. appending tables
    By Bob Burnside in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-12-2004, 12:28 PM
  4. More Tables or Less?
    By Daryl Strickland in forum Alpha Five Version 5
    Replies: 4
    Last Post: 01-05-2004, 08:17 PM
  5. How many tables can you put in in adb?
    By DaveM in forum Alpha Five Version 4
    Replies: 5
    Last Post: 10-11-2001, 05:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •