If an order has separate line items that does not affect it, we are generating one label per order only. The multiple lines show up in the database, but we only print out one per order. The problem I have is that currently in the AS400 database there are 235 shipments, and I want to marry a unique PRO number to each shipment. How do I do that and not reissue the same number, and if I have 235 orders and only 100 PRO numbers how does it know when to stop.
--- In [email protected], [EMAIL PROTECTED] wrote: > Ben, > Are you doing separate shipments for each line? If so wouldn't you need > separate PRO numbers for each line? If you are doing all lines on one > shipment and never ship short, then you should do your make table query > against the sales order header. > > Let me put the question to you another way, do you ever ship multiple > orders as well as multiple lines on one shipment? If so, you have the > classic many-to-many relationship going on. In other words, a sales order > may have one or many PRO numbers because the sales orders may be split or > shipped short or multiple sales orders may ship on one PRO number. > Conversely for the same reasons, one PRO number may have many sales orders. > You will need an intermediate table to join the many to many tables keyed > over the combination of sales order, line number and pro number. Our > business system creates a new line number when a line is partially shipped > so this would work for us. If your business system does not, then the key I > suggested will have to have shipment as the key. > > Please give us further info so we can help you. > Thanks > Hal > > Hal McGee > Engineering Data Manager > Group Engineering - Process and Compliance > Seating Products Group > B/E Aerospace > Winston-Salem, NC > > > > "Ben Norman" > <[EMAIL PROTECTED] > .com> To > Sent by: [email protected] > AccessDevelopers@ cc > yahoogroups.com > Subject > [AccessDevelopers] (Attn: Hal) Re: > 06/14/2005 01:45 ODBC and AS400 Database > PM > > > Please respond to > AccessDevelopers@ > yahoogroups.com > > > > > > > Our AS400 database shows one order but multiple item on order, if you > order five different items on one order it places the information > five times. > > > > > > > --- In [email protected], [EMAIL PROTECTED] wrote: > > Ben, > > I do not understand what you mean by more than one listing per > address? A > > shipment should have only one address, e.g. destination. > > > > Also try to set the make table query to unique records only in the > > properties. This works if you are getting the same record repeated. > > Otherwise this sounds like a join problem. View the query in SQL > view and > > post the SQL so we can look at it and this may help us decipher your > > problem. > > > > Thanks > > Hal > > > > Hal McGee > > Engineering Data Manager > > Group Engineering - Process and Compliance > > Seating Products Group > > B/E Aerospace > > Winston-Salem, NC > > > > > > > > > "Ben > Norman" > > > <[EMAIL PROTECTED] > > .com> > To > > Sent by: > [email protected] > > > AccessDevelopers@ cc > > > yahoogroups.com > > > Subject > > [AccessDevelopers] (Attn: > Hal) Re: > > 06/14/2005 11:58 ODBC and AS400 > Database > > > AM > > > > > > > > Please respond > to > > > AccessDevelopers@ > > > yahoogroups.com > > > > > > > > > > > > > > > > I currently have two tables in my database, one is the ODBC link to > > the AS400 table and the other I populate with PRO numbers given to > us > > by Yellow freight. What my goal is supposed to be is that I assign > a > > PRO number to each shipment automatically through the database, and > > the end results is a 4X6 shipping label with the name and address > and > > a PRO number. The problem is that the database has more than one > > listing per address, so when I assign PRO numbers automatically it > > duplicates them when I run a MAKE TABLE QUERY. > > > > > > > > > > > > > > > > > > > > > > > > > > > > --- In [email protected], [EMAIL PROTECTED] wrote: > > > Ben, > > > Assuming your users won't be confused by the fact that the > > changes they > > > make do not show up on the AS/400, I would suggest the following: > > > > > > Use a Make Table query to make your initial table. However, Access > > will > > > inherit the field properties from the AS/400 and sometimes this > can > > lead to > > > unexpected results. > > > Check the properties and be sure you are ok with them. > > > > > > If not, I use Append queries to get the data from the AS/400 into > a > > local > > > table. Delete queries to clear the local table. This allows me > > better > > > control of the table. > > > > > > I have many databases where I download the data from our AS/400 > > using Task > > > Scheduler and macros. The user gets only the data I download. > > > Also if you are working with very large amounts of data, you > should > > be sure > > > at a minimum to Compact and Close on a regular basis. > > > > > > Good Luck > > > Hal > > > > > > Hal McGee > > > Engineering Data Manager > > > Group Engineering - Process and Compliance > > > Seating Products Group > > > B/E Aerospace > > > Winston-Salem, NC > > > > > > > > > > > > > > "Ben > > Norman" > > > > > <[EMAIL PROTECTED] > > > .com> > > To > > > Sent by: > > [email protected] > > > > > AccessDevelopers@ cc > > > > > yahoogroups.com > > > > > Subject > > > [AccessDevelopers] ODBC and > > AS400 > > > 06/11/2005 08:21 > > Database > > > > > AM > > > > > > > > > > > > > Please respond > > to > > > > > AccessDevelopers@ > > > > > yahoogroups.com > > > > > > > > > > > > > > > > > > > > > > > > > I have a database that pulls all info from an AS400 table via > ODBC, > > but > > > the AS400 table is read only. I want to be able to add info to > the > > > records, but not have to touch the AS400 table. Is there a simple > > way > > > to create a second table mirroring the AS400 table, and be able to > > > populate it automatically with the AS400 info? > > > > > > I need it to be searchable from a report as well. > > > > > > Thank you all. > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > > > > Yahoo! Groups Links > > > To visit your group on the web, go to: > > > http://groups.yahoo.com/group/AccessDevelopers/ > > > > > > To unsubscribe from this group, send an email to: > > > [EMAIL PROTECTED] > > > > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of > > Service. > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > Yahoo! Groups Links > > To visit your group on the web, go to: > > http://groups.yahoo.com/group/AccessDevelopers/ > > > > To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service. > > > > > > Please zip all files prior to uploading to Files section. > > > Yahoo! Groups Links > To visit your group on the web, go to: > http://groups.yahoo.com/group/AccessDevelopers/ > > To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
