Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13

    Database Design

    Ref: https://www.accessforums.net/general...ome-48112.html

    I've attached the .odb to this message. I'm not sure how much repair work to this could/should be done if another direction seems advisable. Part of the way this works is that small, scanned images of our custom dye colors appear in the Dye Colors form, and that's a feature I don't want to loose. The three main issues that I see right now:



    1. Colorways form (colorway is largely a British term that refers to something, like yarn, that includes more than one color) - Treatments sub form - "Order of Treatment" field doesn't auto complete as it should.

    2. Also in the Colorways form, we sometimes repeat the same procedure. So far, there's no way to handle those repeats; the db won't allow duplicate records. One idea is to include a quantity field that would auto complete & default to 1, but allow me to change that to any other integer. As this is a production tool, it would be helpful to allow the user to check off the steps that have been completed. These colorways are produced over a period of many days; we have any number of colorways in production at any given time, and either one of us may be carrying out the next step. One suggestion is that we use a production log or logs to facilitate this, but we weren't able to get that far.

    3. Several table & field names need to be changed, but that's not a simple task in a relational db, at least not for me.

    I'd value any insight, suggestions for approach, or any other detail that seems important. While such a figure as you quoted for a job 15 years ago is completely out of our reach, we both have considerable experience in graphics work, both 2D & 3D, and an offer for a trade of services would be welcomed.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not seeing an attachment. I split your post out to a new thread.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13
    Apologies, .odb is attached below. For ease of understanding context, my first post to the original thread was,

    Josh, as it happens, I'm looking for some help in that area. My adult son & I have started a small business. We dye wool yarns for knitters and other fiber workers by hand. This involves keeping track of the quantity of the commercial dyes we use to make one of our custom colors. It also involves keeping track of the quantity of those custom colors that are used in one of the variegated color yarns we make.

    Several weeks ago, for the second time, I saw that our data was getting away from us, and attempted to build a db. I made an attempt to do this with OpenOffice by myself, but quickly realized how little I know as compared to what I needed to know to do any justice to the db. The only copy of MS Office I own is Office 2000.

    We got a good deal of help from one gentleman, and much of the design work is done; I've entered a fair amount of data. However, his job was just ramped up, and he doesn't have the time any more to continue. The idea was to get the db working in OOo, then connect to a MySQL db somehow. I believe he preferred Access, but either doesn't have a copy of 2000, or wanted to use something newer. The MySQL work has not been started yet. If we can get it running in Access 2000, maybe the MySQL won't be required...I don't know a lot, and it's a bit confusing.

    Currently, I'm completely stuck, and am not finding solutions to a couple of issues, so the db is not able to be used as intended yet. The db is relational, and so far, includes 6 tables, and 4 forms. If this interests you or another forum member, how would you like to proceed?
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I was hoping .odb was a typo. What is an odb file? I can't open this file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13
    That's the native database file format for OpenOffice.org (OOo) Base files.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is an Access forum. Don't see how we can help much with an OpenOffice database issue.

    1. why should field 'auto complete', what do you want it to fill with?

    2. why does the table prevent duplicates? A totally duplicate record really doesn't make sense anyway - should be something different such as record ID or date or whatever

    3. Access handles this to some degree and there are inexpensive third party add-ins that do a more thorough job easily - I use Rick Fisher's Find & Replace, saved my sanity more than once
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13
    I understand that this is an Access forum; it's why I'm here. I am aware that this is not an access db. From my original post,
    The idea was to get the db working in OOo, then connect to a MySQL db somehow. I believe he preferred Access, but either doesn't have a copy of 2000, or wanted to use something newer.
    Access is fine for me if I can get by with my old 2000 version. You asked for a file. This is all I have right now. I feel no need to use OOo, in fact, it's the least appealing to me in the long term.

    1. The field to which I was referring is basically an ID field. It was designed to auto complete to save time in data entry.

    2. I don't know why the table prevents duplicates. When I asked, he gave me the link to a massive, 260 page manual in .pdf format; my eyes glazed over many times trying to find what he wanted me to read, but I never did. The ID isn't auto completing, and I can't enter data in that field, neither will it allow me to enter the same data twice. It stops responding until I change something. There is no efficiency in the ways I've tried this, and so I came here to request suggestions, and help, with the possibility of moving away from OOo entirely very present in my mind. I apologize for not having made that clearer.

    3.
    Access handles this to some degree
    If by 'this', you mean referential integrity, that has been a point of both confusion & frustratioon. The gentleman who was helping me until late last week said,
    My prejudice is to avoid the dangerous simplicity of automatic cascading deletions or updates. It is too easy to make inadvertent far reaching errors. I prefer the more tedious restraint that requires all referencing records in the child table to be deleted or updated before making the change in the parent. It is, however, a matter of choice and the best route depends on circumstances and how the database is used.
    I took his advice, and tried to learn in the way he suggested, but it was not a success, and more than an entire day was spent in reading and trying.


    If possible, I'd like just to move forward from this point.
    Last edited by Dyers; 11-06-2014 at 02:36 AM. Reason: edited for clarity

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Certainly can't provide any guidance on why the autonumber field is not working, if it is truly an autonumber field and not really a code-generated custom unique identifier.

    For item 3 I meant the changing of names.

    Don't disagree with the viewpoint about setting static relationships. Structure of forms and use of code can manage referential integrity. Depends on the db and how much code I want to write. Ideally, users do not interact directly with tables and queries, only with forms and reports.

    From here, if you want to migrate to Access, start building database. Will be happy to review it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Currently, I'm completely stuck, and am not finding solutions to a couple of issues, so the db is not able to be used as intended yet. The db is relational, and so far, includes 6 tables, and 4 forms. If this interests you or another forum member, how would you like to proceed?
    So I was curious....I took a look at your .odb. I can see/edit the tables but can't open the forms for viewing or editing. Could be because I have Win 7... I'll try looking at the dB over the weekend using Win XP.
    I didn't see any queries or any type of code??
    What version of OO are you using? (I installed 4.1.1)

  10. #10
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13
    Hi, Steve;

    I'm using Win8.1, and OOo 4.0.1 if that helps...but I'm working to convert the odb to mdb, and as far as I can see, there's no tool to do that. I found directions to convert each odb table to csv, then import those into Access. I don't know yet if there's a way to import the forms, or if those have to be recreated. I'll post what I have when I reach a point that I can't go further on my own. There aren't any queries yet, and I don't know anything about code. It does have at least 1 macro, but I don't remember if the gentleman who was helping me told me about it/them yet or not.

    Dave

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Great

    3. Several table & field names need to be changed, but that's not a simple task in a relational db, at least not for me.
    Now is the best time to make these changes.. get the table structure correct.

    I'll keep an eye out for you...

  12. #12
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13

    Question Names of forms, tables, and fields defined; issues with Colorways form

    I'm not ready to give up yet. I do have a question, though. I believe OOo calls them sub forms, but in any case, I have a form that looks like this. There are 2 of my 6 tables involved here. Form design would take some study to use. I used the wizard, and created the top portion with it's corresponding table, but I'm not finding a way to include the 2nd table.

    A little background might help.

    My adult son & I are yarn dyers. We buy our dyes in a highly concentrated powdered form, and stock something like 20 commercial colors. These are referred to as Dye Powders in the OOo db. Our first job is to use any number of these (usually no more than 4) to create our own colors. In Base, these are called Dye Colors, and the formulas we use to create them are referred to as Recipes. A formula includes the ID of the dry powders, and it's weight (g) in the recipe. These become stock solutions when mixed in a volume of water, and they allow us a great deal of precision in measuring the tiny amount of dry powder that is needed for dyeing. Our second job is to combine our dye colors in such a way as to produce variegated yarns called Colorways. Press here for a glimpse of a few. The colorways are more complex to describe because there are quite a number of methods we use to apply the dyes to the fiber. Most colorways are made with up to 20 layers named Treatments in this db. A treatment will include a dye color, a volume (cc), and a Procedure.

    How can I import that 2nd table to this form? The wizard says I can pick from multiple tables / queries, but the drop down only contains 1 table name.

    I want to correct / update some of the nomenclature either because it's incorrect, or because the edit could bring clarity. I'm thinking I should hold off on that until I get things stable in Access before attempting any changes, though; does that sound like a reasonable approach?

    There are two issues with this form as it exists in OOo Base that I'd like to avoid with Access.

    1. The Order of Treatment field in the Treatments sub form is supposed to auto complete. That it doesn't creates a problem in that it is the field used to sort the records...it's the field that tells us the order of the various treatments. If it's null, then OOo defaults to the next field to it's right, the dye color id.
    2. We sometimes repeat a step. OOo doesn't allow duplicate records, so I considered a quantity field that would default to 1, but allow me to overwrite it, or select a different integer from a list box. However, we will be using the database from a computer in the 'studio', an old canning kitchen in our basement, as a production tool. It will be necessary to designate a treatment as having been completed. I would like to find a way to force the user to so designate it before the next record can be accessed. My original plan was just to make a check box in it's own field, and when a step was completed, it would be marked. However, this would require that each step had it's own record (eliminating the need for a quantity field), and that's not allowed in OOo. Would you employ a production log? How difficult are they to create / use?
    Last edited by Dyers; 11-07-2014 at 05:17 PM. Reason: to complete an idea

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I want to correct / update some of the nomenclature either because it's incorrect, or because the edit could bring clarity. I'm thinking I should hold off on that until I get things stable in Access before attempting any changes, though; does that sound like a reasonable approach?
    I would make the changes now (in Access). Much easier to make the changes now, rather than to wait until the db is "done" (usable) and have to find everywhere a name/variables is used. Build it right from the start.

    How can I import that 2nd table to this form?
    A subform is just what it says: a form within a form. In Access, if you want to add a subform, first create the subform, then drag it into the main form.

    note: I base all of my forms on queries, not tables. Tables have no order; a table is just a bit bucket. It is easier (IMO) to sort and/or filter using a query.

  14. #14
    Dyers is offline Novice
    Windows 8 Access 2000
    Join Date
    Nov 2014
    Posts
    13
    Quote Originally Posted by ssanfu View Post
    I would make the changes now (in Access). Much easier to make the changes now, rather than to wait until the db is "done" (usable) and have to find everywhere a name/variables is used. Build it right from the start.
    I see your point. I tried editing the csv files before importing them to Access; that seemed to work as expected. That saved me a lot of aggravation; thanks.

    A subform is just what it says: a form within a form. In Access, if you want to add a subform, first create the subform, then drag it into the main form.
    This seems straightforward enough. I seem to have missed a step, though, and am not discovering it just yet. I have 2 forms that each have a sub form. They seem to be displaying correctly in design, but they don't display at all otherwise. When I save, close & reopen them, they're just as I left them. You can see here, or download the db if you care to look. Also, all 6 forms display on the db list; I might have thought only 4 of them would?

    note: I base all of my forms on queries, not tables. Tables have no order; a table is just a bit bucket. It is easier (IMO) to sort and/or filter using a query.
    I honestly don't know much about queries yet, and I have no working familiarity with them. I have a fairly comfortable relationship with tables at this point. With the learning curve as steep as it is still, wonder if I stick with the tables for now?

    What do I need to do to link the image box with the image url for the in focus dye color, as well as to label it?
    Attached Files Attached Files

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A form can be bound to table then filter/sort can be done by using the intrinsic tools from the menu bar or right click menu or by code to set properties of form.

    All of the forms are set for Datasheet view. You probably should switch to Single or Continuous View unless the form is intended to be a subform and even then Continuous view is more versatile. A form in Datasheet view will not show a subform, have to click the + in the left margin to expand the subform. Either way, need to set the Master/Child Links properties of the subform container control.

    The value in Image URL field is not a URL, it is just a file name. Do you want to be able to display these png images on form or report? Are these images stored in a folder on your computer? Dynamic display of images would be easy in Access 2007/2010/2013. Earlier versions require code. https://support.microsoft.com/kb/148463?wa=wsignin1.0

    Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention. Why is the textbox bound to a field (Dye Color ID) that doesn't exist in the form's RecordSource?

    I think you could truly benefit by completing an introductory tutorial book before tackling db design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  2. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM
  3. DataBase Design.
    By cap.zadi in forum Database Design
    Replies: 3
    Last Post: 09-24-2011, 02:54 PM
  4. Database design
    By BeetleBailey in forum Database Design
    Replies: 6
    Last Post: 09-20-2011, 01:35 PM
  5. Database design - PLEASE HELP!
    By wanderliz in forum Database Design
    Replies: 1
    Last Post: 08-22-2010, 10:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums