Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a Customers table that has a field called: Event that is connected to the Event table. I know this probably sounds strange that the customer is not in the event table. This is for several reasons: Customer information will always be imported in from Excel with an event. So for example: CustomerID, FName, LName, Address, City, Province, PCode, PhoneNo, Cell, Event. The customer only ever has one event connected to him. My background with Access really wants to make the relationship the other way: Event with the customer connected using a form and subform, etc. Anyhow, so this is the way I did it because info is always imported from Excel could be once a week.

    My issue is that because the event field in the Customer table is a number field (because it connects to the primary ID key field in the Event table which is an autonumber field) when I try to import some sample data to test it, it always skips the Event information from Excel. I tried tricking Access by formatting the field in Excel to be a number and even tried leaving the top rows in Excel as numbers and my real info underneath, it simply does not work. I would greatly appreciate any feedback. If worse came to worse, the customer could be matched to the event after importing all the Customer info, but I was hoping this does not happen because there are about 200 customers imported at one given time.

    Any suggestions would be appreciated.

    Kind Regards,

    Tina

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I think we are going to need to see some sample data in the Excel format as well as the Access database to be able to spot what is going wrong. One possible issue is that the event code doesn't exist in your Event table, so the data is dropped. Another is that Access expects numeric data, and the Excel workbook is treating it as text. You might try importing the data into a new table, and then doing an append query to move that data to the Customers table. It might also help to understand a bit more about the problem you are trying to solve with the Access database.
    Wendell

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much for your reply. I figured it out. What I did was put the corresponding number in the Event field and it imports in as the text Event (because of the relationship with the Events table to the Customers table). Interesting thing though: when I tried to automate this and used code behind a button to automatically import, it skips that Event field. When I built a macro, it works perfectly. The code uses the Transfer Spreadsheet Do Cmd and the macro uses the Import command, which works perfectly.

    Anyhow, thanks,

    Tina

Posting Permissions

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