Results 1 to 3 of 3
  1. #1
    Hendro623 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    14

    Question Data Handling Issues- Appending Data with a linked Excel Spreadsheet plus ability to add records

    This is officially my first post on a forum, hoping for some good assistance

    I'm having issues trying to figure out the best way to design the structure of my database. As I manage to figure one thing out I come across a snag that forces me to go back and start over.

    Basically I have a report that is generated out of Crystal Reports into an xls file. Right now I have the file linked to my database with an append qry to split and update 2 tables I have to separate the Order Information and order Details. It appends fine but there are 2 other factors that come into play that are causing me to rethink how I have this set up.

    1. If something is changed such as the Ship-Date on the crystal report, it doesn't change due to the order already being a record. With duplicates not allowed it doesn't append anything, trying to figure a way around this without them having to manually change it in the database.



    2. Manually adding data that is not in the linked report, to these tables. I keep running into issues that prevent it from being added to the appropriate tables such as key violations. I started going another route by creating a unique OrderLineID that concatenates "Order_num, Item, & Lot" together. That was working but when I tried creating the form for that value to auto populate it kept telling me I was missing the value on the form.

    I really just need to know whether or not i'm going about this the wrong way. Does anyone have any other method/idea that may work better with my scenario? I've attached my relationship setup along with the headers of the linked excel file if that helps. If more details are needed let me know, any assistance is appreciated!!
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	17 
Size:	112.2 KB 
ID:	26777

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You seem to be doing it wrong.
    You don't alter the report, you update the access FE,then it flows out to reports and excel files.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum....

    Comments in line......

    Basically I have a report that is generated out of Crystal Reports into an xls file. Got this!

    Right now I have the file linked to my database with an append qry to split and update 2 tables I have to separate the Order Information and order Details. It appends fine but there are 2 other factors that come into play that are causing me to rethink how I have this set up.
    Does this mean you have the Excel file linked to the Access dB and you have an append query to append data FROM the Excel worksheet TO Access tables?

    1. If something is changed such as the Ship-Date on the crystal report, it doesn't change due to the order already being a record.
    Crystal reports allows you to CHANGE data in the dB??? I would think you would have to/want to change the [Ship-Date] in the dB first.

    With duplicates not allowed it doesn't append anything, trying to figure a way around this without them having to manually change it in the database.
    Don't understand this at all......

    2. Manually adding data that is not in the linked report, to these tables. I keep running into issues that prevent it from being added to the appropriate tables such as key violations.
    Also don't understand this......

    I started going another route by creating a unique OrderLineID that concatenates "Order_num, Item, & Lot" together. That was working but when I tried creating the form for that value to auto populate it kept telling me I was missing the value on the form.
    This can be/should be done (calculated) in a query. I see "Order_num" and "Lot".... don't see "Item". But why would you want to save it?

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

Similar Threads

  1. Importing Excel spreadsheet and appending it to an existing table
    By Access_Novice in forum Database Design
    Replies: 3
    Last Post: 08-21-2014, 07:01 AM
  2. Replies: 1
    Last Post: 07-08-2014, 06:22 AM
  3. Replies: 14
    Last Post: 04-21-2014, 08:08 AM
  4. Replies: 1
    Last Post: 11-26-2012, 12:35 PM
  5. importing excel spreadsheet issues
    By majortool in forum Import/Export Data
    Replies: 1
    Last Post: 01-24-2008, 06:29 PM

Tags for this Thread

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