Results 1 to 7 of 7

Creating DB, Issue with Calling Fields from other Tables with No Relationship

  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Creating DB, Issue with Calling Fields from other Tables with No Relationship

    I am creating a DB from an Excel Workbook that has a lot of input fields and formulated fields. My main problem is that altogether the Excel fields add up to more than 255. My plan was initially to use one table and to create my forms and reports off that (since normal relationships won't work on SharePoint).

    It seems that I am forced to use at least two tables. I am aware that I can use the Lookup Wizard to grab values from another table, but I was hoping there was a better, faster way.

    At the moment I have two solutions:

    1) Create one primary table with all the calculations and a reference like Date. The other table uses information that is not going to be calculated (i.e. Name, Company, etc.) and will have a field that uses the Lookup Wizard to see the primary table's Date. The Date is required forcing the user to pick a Date in order to proceed to other related forms (I am trying to avoid having them choose the date every time they open a form, so having one form only appear after another would seemingly allow this). There is then a macro that somehow goes to the Date value chosen, and then opens the next form. This would allow the other tables to use the same date for their values and eliminate any error on the user's part.

    For example: The first form has a button that states "Proceed". I fill out some of the info except date. I click the "Proceed" button and it gives me an error message telling me to choose a date first. I choose a date and then click the button to advance to the next form which will not show the date, but would know from the macro that it is the same date.

    I don't know how I could keep the Date value if using a macro. Does anyone know how to setup a macro to do this?

    2) I create at least two tables a lot like in the first solution. I then create multiple fields as Lookup Wizards datatypes in the table with the most fields. For each of these fields I select 20 fields until I have selected all of the fields from the table with the least fields. I then create a union query to made up of all the fields in the table with the most field (including the Lookup Wizards). I then create forms and reports from this union query since it holds all the data in one place.

    What I don't know about this solution is if (a) will a union query work on SharePoint? (b) Will the union query allow more than 255 fields to be used? (c) Will the union query show each of the 20 fields from the Lookup Wizard fields, or will they remain as the single value chosen?

    If anyone has any better idea on how to set this up, please help! I am hoping the first solution will work, but I am not sure how to setup the macro to check for the Date chosen the previous form.

    Thank you for the help.

  2. #2
    RayMilhon is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    733
    First Understand that Excel and Access are 2 different applications. To convert your spreadsheet into an Access Database read up on Database Normalization. For most databases you want the database in 3rd normal form. Once you understand that you can throw away both of your solutions as neither is any good for a DB.Second you use a query to get your data not a macro. Calculated Fields in your Spreadsheet would not be stored in your tables at all. They can be calculated within the query/form/report.

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question

    Hi Ray,

    I took the time to find Database Normalization and checked out the 3rd manner of which to do this.

    This has been helpful as it led me to Projected Fields, which seems to allow a connection between two tables and be able to display all other columns of the other table if needed. But I have a few more questions on this subject:

    *1) Everything I have read seems to show how to setup Projected Fields in SharePoint, but not how it would be done in Access. If I could create one LookUp Field that connects to the main table I could then use Projected Fields to show all of the information for both tables. How is this done in Access? Or is it only possible when in SharePoint (in which case I need to design in SharePoint...?)?

    2) A query would be perfect to use as it would consolidate both tables. Once I create a LookUp Field between the two tables will I be able to use all the fields from both tables? I ask this because I believe queries have a 255 field limit, which is the real issue.

    3) If the query works as hoped in #2, will I see all the fields when working on a form/report, or will the LookUp fields just show the selected Field?

    I am assuming that Projected Fields would be needed for #2 & #3 since a normal LookUp Field will only allow up to 20 fields...

    Thanks for your help! I think I'm getting much closer to a solution.

  4. #4
    RayMilhon is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    733
    1. Not exactly sure what you mean by Projected Fields but what I think you're referring to are called Foreign Keys by every Database Developer I know. Here's an Example: Say you have a business and your developing an Employee Database. You would have 3 tables. 1 for all employees, 1 for the department and a third to show which employees report to which supervisors. We'll ignore the last one for the moment.tblEmployeeEmployeeID AutoNumber Primary KeyEmployeeName TextEmployeeAddress TextDeptID Number Foreign KeyTblDepartmentDeptID AutoNumber Primary KeyDepartment TextSo the employee data would look like this1 Ray Milhon 12345 My Street 12 John Doe 54321 Your Street 1the Department data would look like this1 Information TechnologyYour Query would be like this: Select a.Name, b.Department From tblEmployee a Inner Join tblDepartment On a.deptid = b.deptidThe Results would look like thisRay Milhon Information TechnologyJohn Doe Information TechnologyI Never use lookup fields on a table. Probably because I go back to Access 2.0 and never really saw a need. A form/Report can have as many fields as you need. Without seeing your spreadsheet I can only guess but I've never found a database I needed all 255 fields. The most actual data fields I've used is around 100. Everything else was calculated in the form/report. If you can attach the spreadsheet. I could take a look.

  5. #5
    RayMilhon is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    733
    These always look better when I type them then when I post them. None of the spacing or columns are there. Wonder why?

  6. #6
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    The reason for LookUp tables is because Sharepoint does not use normal relationships like Access does.

    The number of fields I need to use is the number of both input fields and calculated fields from the Excel sheet, which unfortunately go over the 255 limit.

    Projected Fields are something used by SharePoint that seem to allow the use of all other field values from another table by use of a LookUp field. I actually had found Foreign Key information that led me to Project Fields. I can post the link I found if it helps at all.

    The main problem is not so much being able to create the DB in Access; the problem is the restrictions and limitations SharePoint imposes when uploading an Access DB.

  7. #7
    RayMilhon is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    733
    Well then your out of my expertise. Sorry I couldn't help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relationship issue
    By Calgar99 in forum Access
    Replies: 4
    Last Post: 12-14-2011, 05:36 PM
  2. relationship issue or filter by subform?
    By flwrgrl in forum Forms
    Replies: 9
    Last Post: 07-14-2011, 01:07 PM
  3. Relationship screen Issue!
    By Lincoln in forum Access
    Replies: 8
    Last Post: 07-14-2011, 03:16 AM
  4. Calling fields into VBA Private Sub
    By fullshape in forum Programming
    Replies: 3
    Last Post: 02-18-2011, 07:22 AM
  5. Calling Stored Proc in MS Access 2007 without creating query?
    By DistillingAccess in forum Programming
    Replies: 1
    Last Post: 08-03-2010, 08:38 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
  •  
Tech Forums: Microsoft Office Forums