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!!