Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55

    Exclamation Help with combining records needed urgently!


    Okay, I have only two days left to have my little project finished. When I was done with it a co worker found a glitch and I must resolve it but don't know how. here's my delima.

    I have a table with records:

    ID|name|address|phone|equipment|Installation date|Disconnect date|

    I need to be able to combine data like this.

    1) ID|name|address|phone|equipment| installation date|disconnect date
    2) |equipment| |Disconnct date

    the end result being record 1 updated with record two's information "equipment and disconnect date. Record two being deleted and a new record #3, showing the abandoned data from record one, equipment and disconnect date:
    1)ID|name|address|phone|equipment|installation date|disconnect date
    2) #DELETED
    3)equipment|Disconnect date (from line one moved to new record making it available for future use)

    I need to be able to update record one with the combined data displayed above. on a case by case basis.

    Any help would be appreciated. Thanks.

  2. #2
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    It looks like you need two tables in a one-to-many relationship. Could a person have more than one Installation Date? I'm assuming they could. Here's an example table schema:

    Table: People
    PK: PersonID
    Name
    Address
    Phone

    Table: PersonEquipment
    PK: PersonEquipmentID
    FK: PersonID
    EquipmentName
    InstallationDate
    DisconnectDate

    Now a join query could bring this information together for your report.

    You can make a "person form" and include a "PersonEquipment" subform on the person form.

    You could expand this schema to include an Equipment table, if you are adding new equipment to your inventory from time time. Ex.:

    Table: People
    PK: PersonID
    Name
    Address
    Phone

    Table: PersonEquipment
    PK: PersonEquipmentID
    FK: PersonID
    FK: EquipmentID
    InstallationDate
    DisconnectDate

    Table: Equipment
    PK: EquipmentID
    EquipmentName

  3. #3
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by pdebaets View Post
    It looks like you need two tables in a one-to-many relationship. Could a person have more than one Installation Date? I'm assuming they could. Here's an example table schema:

    Table: People
    PK: PersonID
    Name
    Address
    Phone

    Table: PersonEquipment
    PK: PersonEquipmentID
    FK: PersonID
    EquipmentName
    InstallationDate
    DisconnectDate

    Now a join query could bring this information together for your report.

    You can make a "person form" and include a "PersonEquipment" subform on the person form.

    You could expand this schema to include an Equipment table, if you are adding new equipment to your inventory from time time. Ex.:

    Table: People
    PK: PersonID
    Name
    Address
    Phone

    Table: PersonEquipment
    PK: PersonEquipmentID
    FK: PersonID
    FK: EquipmentID
    InstallationDate
    DisconnectDate

    Table: Equipment
    PK: EquipmentID
    EquipmentName

    Okay, right now the information is being gathered from query, "customersExtended" the idea is this:

    Customer A is on equipment LET-01-02-03 and is fed on cable pair 2
    Customer B is no longer a customer but was on LET-02-05-04 Pair 4

    Customer A's equipment goes south and needs to be moved to old customer B's equipment. We can move them but don't want to lose track of where B's cable pair 4 is going so we want to retain that data. Furthermore, Customer A's equipment will eventally be repaired so we want to maintain that data as well.

    I'm very new (three weeks) to access and am trying to grasp the concept of your idea. If I create another table, or two tables, how will all of my information be stored on the primary "customers" table?
    Is there no way of doing this with a single table without having to manually enter the table to make the changes? *not a bad option from where I am right now*
    Thanks again for your help and your anticipated response!

  4. #4
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    You may need a "CablePair" table as well, and a table to link "CablePairs" to Equipment. I.e.: "CablePairEquipment".

    This is not a 2 day project for someone who has only been using Access for 3 weeks, and who is just getting used to the concept of a relational database.

  5. #5
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by pdebaets View Post
    You may need a "CablePair" table as well, and a table to link "CablePairs" to Equipment. I.e.: "CablePairEquipment".

    This is not a 2 day project for someone who has only been using Access for 3 weeks, and who is just getting used to the concept of a relational database.
    Okay, I think I'm getting it.

    Cable Table.
    ID|cable pair|Phone Number|Address

    Equipment Table
    ID|equipment numb|Phone Number|

    Customer table
    ID\FName|LName|Address|Install_date|DiscDate|Phone No.

    Create a Query
    using Phone number as relationship
    QCustomerData

    ID|equipment| Cable Pair|FName|LName|Address|Install_date|DiscDate|Phone Number
    FROM QCustomerData ?
    Something like this???

  6. #6
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    Quote Originally Posted by danbo View Post
    Okay, I think I'm getting it.

    Cable Table.
    ID|cable pair|Phone Number|Address
    Does a Cable have a phone number? That doesn't seem to make sense to me. A table is an "Entity". Fields are "Attributes" of that entity. PhoneNumber is not an attribute of a Cable (as far as I'm aware).

    Quote Originally Posted by danbo View Post
    Equipment Table
    ID|equipment numb|Phone Number|
    Does a piece of Equpment have a phone number? (Same reasoning as above.)

    Quote Originally Posted by danbo View Post

    Customer table
    ID\FName|LName|Address|Install_date|DiscDate|Phone No.

    Create a Query
    using Phone number as relationship
    QCustomerData

    ID|equipment| Cable Pair|FName|LName|Address|Install_date|DiscDate|Phone Number
    FROM QCustomerData ?
    Something like this???
    No.

    Before you begin, you should become familiar with:

    Fundamentals of Relational Database Design http://www.deeptraining.com/litwin/d...aseDesign.aspx

  7. #7
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by pdebaets View Post
    Does a Cable have a phone number? That doesn't seem to make sense to me. A table is an "Entity". Fields are "Attributes" of that entity. PhoneNumber is not an attribute of a Cable (as far as I'm aware).



    Does a piece of Equpment have a phone number? (Same reasoning as above.)



    No.

    Before you begin, you should become familiar with:

    Fundamentals of Relational Database Design http://www.deeptraining.com/litwin/d...aseDesign.aspx
    Okay, I'll check it out but your advice has already helped. I have created four tables.
    Address, Cable, Customer, Equipment
    I related the ID of each of the tables (auto number)
    then I created a query that calls the information from each of the tables as needed and then a form that I can use to update that information. I "think" this might work but it hasn't been put to the test just yet. I'll read the article that you just sent.
    Thanks!!

  8. #8
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by danbo View Post
    Okay, right now the information is being gathered from query, "customersExtended" the idea is this:

    Customer A is on equipment LET-01-02-03 and is fed on cable pair 2
    Customer B is no longer a customer but was on LET-02-05-04 Pair 4

    Customer A's equipment goes south and needs to be moved to old customer B's equipment. We can move them but don't want to lose track of where B's cable pair 4 is going so we want to retain that data. Furthermore, Customer A's equipment will eventally be repaired so we want to maintain that data as well.

    I'm very new (three weeks) to access and am trying to grasp the concept of your idea. If I create another table, or two tables, how will all of my information be stored on the primary "customers" table?
    Is there no way of doing this with a single table without having to manually enter the table to make the changes? *not a bad option from where I am right now*
    Thanks again for your help and your anticipated response!

    you have PK: and FK: Is that designating something? FYI, like you didn't already know... my master plan failed.

  9. #9
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55

    Question

    Quote Originally Posted by danbo View Post
    you have PK: and FK: Is that designating something? FYI, like you didn't already know... my master plan failed.
    Okay, I got it... it's been too long of days! Primary field and Forein Fields. Now I have a whole new issue. I did what you suggested and started with a blank DB. I have the following tables

    Customers
    Equipment
    Cable

    I have created a query "customers" that pulls the data like you explained. I then created a form based on that query and everything works except that the information is sent directly to the tables, which is good. the problem I am having now though is that the data wont disply on the query alone. Did I miss a step?

  10. #10
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    I don't know what you mean by "the data wont disply on the query alone". Queries are views to your table data. Queries do not contain data themselves.

  11. #11
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55

    Unhappy

    Quote Originally Posted by pdebaets View Post
    I don't know what you mean by "the data wont disply on the query alone". Queries are views to your table data. Queries do not contain data themselves.
    I understand that but what it is calling from the tables wont display in the query datasheet.

    Table - Customer
    Fname Lname Phone
    Mark Davis 736-9797

    What is displayed in the query results

    Fname Lname Phone


    Once I open the query I thought it would display the results from each table that it is calling from. At least that's how it's always worked on this thing before. I have all of the boxes checked to be visible too.

  12. #12
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    What is the query SQL?

  13. #13
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by pdebaets View Post
    What is the query SQL?
    SELECT people.CustomerID, people.FName, people.LName, people.[email address], people.website, Equipment.[RST/LET], Equipment.CRV, Cable.[Cable Pair]
    FROM (Equipment INNER JOIN Cable ON Equipment.[EquipmentID] = Cable.[FK: EquipmentID]) INNER JOIN people ON Cable.[FK: EquipmentID] = people.[FK: EquipmentID];

  14. #14
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    First, don't name the field "FK: EquipmentId". "FK" means foreign key. Name the field "EquipmentID". Same for the other fields.

    Next, there should be no EquipmentID foreign key on the People table.

    This isn't the right query SQL. There's no phone number in the field list, but you've shown phone number in your sample results.

  15. #15
    danbo is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    55
    Quote Originally Posted by pdebaets View Post
    First, don't name the field "FK: EquipmentId". "FK" means foreign key. Name the field "EquipmentID". Same for the other fields.

    Next, there should be no EquipmentID foreign key on the People table.

    This isn't the right query SQL. There's no phone number in the field list, but you've shown phone number in your sample results.
    GRRRRRRRRRRRR... Okay. It's working like a champ ALMOST! Now I get an error on the cable pair data entry. "Cannot enter value into blank field on 'one' side of outer join."
    I'm sure it's a relation error. It works if I enter the data into that field first but not last.

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

Similar Threads

  1. Combining Records
    By admessing in forum Queries
    Replies: 2
    Last Post: 12-15-2011, 05:41 PM
  2. Help with Combining multiple Records
    By Jennivere in forum Queries
    Replies: 1
    Last Post: 11-28-2011, 08:05 PM
  3. Query for combining records
    By alpinegroove in forum Queries
    Replies: 6
    Last Post: 01-28-2011, 07:29 AM
  4. Combining / Merging Records
    By alpinegroove in forum Access
    Replies: 8
    Last Post: 01-27-2011, 09:43 AM
  5. combining field from two records into one
    By RedGoneWILD in forum Queries
    Replies: 8
    Last Post: 07-13-2010, 09:47 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 - Senior Forums