Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking fields from different tables (Access XP)

    I am trying to create an inventory database. I am trying to use the Inventory control database created by the Access Database wizard. We use a Palm Pilot with a barcode reader to hand count our inventory weekly. This database is then uploaded to an Excel spreadsheet that does the calculations to tell us how much inventory to order. But with the Excel spreadsheets I have no way of tracking what we order from week to week and to get monthly and yearly totals.

    I would like to be able to create a database that would produce PO's for the order we place every week thereby creating a history of how much we order for each week, month and year. I would also like the PO's to then update my total inventory when we receive the shipment.

    After all that here is my first question. Is there a way to link cells in an Excel spreadsheet to fields in an Access table? This would eliminate me having to enter barcode numbers, descriptions, etc. into the Access Database.

    Second question; Is there a way to then link fields from one table to another? Or would this only be done in a query?

    BTW should anyone already have a working application as I have described I would be willing to talk with you about it.

    Thanks,


    Bret

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking fields from different tables (Access XP)

    What is the format of the Palm Pilot data? Is it a txt file?
    If it can bel loaded into excel it can be loaded into Access.
    If it has to go via excel you can import the entire sheet into an Access table.
    You can manipulate the data from there.

    You can link tables by a similar key.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking fields from different tables (Access XP)

    Yes the Palm file is a tab delimited text file. I haven't had any luck in teaching myself how to do mathematical functions in Access. I use the Excel sheet to do the math. I have imported the Excel file as a linked table into the Access database. But now I need to know how to update a product table when I hot sync the Palm to the PC.

    Here is what I currently do with the Excel file. I have a sheet that imports the tab delimited txt file from the Palm folder that the Palm Hot Sync's to. Then I have linked that sheet to a second sheet same workbook that takes the count of inventory on and and it subtracts the amount we want to keep on hand from the amount we have. That number then determines how many we need to order. That cell is then linked to a second workbook that contains a replica of the hand order form(hard copy) our warehouse requires. They don't use computers, they still enter each customer's order on an order pad.

    I plan to create a report in Access that will give us a PO that can be given to them over the phone in the same order as their order pad (saves on errors to follow their form).

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking fields from different tables (Access XP)

    You can update the Product table with an UPDATE query joining on ProductID (I presume that is the link between the two tables).
    You would then update the imported table to set a flag that says you have updated the Product table (so you don't do it more than once).
    You can then run your report like you said.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking fields from different tables (Access XP)

    Thanks for all the help Patti. Now I am running into a problem with how to name the field to be updated. For example I have a field in both tables named Barcode. What is the stucture for the "Update To:" cell in the query?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking fields from different tables (Access XP)

    You have to qualify it with the Table name, otherwise like you say, it's got no idea which one you are referring to.
    HTH
    Patrick <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking fields from different tables (Access XP)

    Sorry for the Patti, Patrick. Thanks for the help I guess I am dumber than dirt.

    Bret

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking fields from different tables (Access XP)

    Glad to be of help.
    Don't be so hard on yourself, I didn't know these things at one stage either.
    It takes a lot of time to learn Access, and just remember, nobody knows it all, although Charlottes pretty close.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: linking fields from different tables (Access XP)

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15> You are too kind, Pat. It's easier when you've been working with it since the day it was released. All the new stuff comes along in installments! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> My heroes are the guys who write the Access Developers Handbooks. Goodness knows how they figured out some of the stuff in there. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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