Wow...Thanks John. Will give it a try. Greg
--- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > Greg- > > OK. First create a new empty table. Select SANDER TABLE and copy it to the > clipboard. Choose Paste, select the option to paste structure only, and > give it a new name - perhaps SANDER MERGED. Open the new table in Design > view. Open tblDowngrades in Design view. Select the extra fields one at a > time (Lot #, Blows, RejBrokenPanels, ShpBrokenPanels, Fissures, SanderKO, > Pits, SandThrough, SanderSetUp, Spots, etc.), copy the field to the > clipboard, and paste it onto the blank row at the bottom of the field list > in SANDER MERGED. If Blows through RejMisc are all together in > tblDowngrades, you can select them all at once and do one copy/paste > operation. Save the changes to SANDER MERGED. > > Now, create a UNION query. Start a new query on SANDER TABLE and then > choose SQL View from the View menu. The SQL needs to look like: > > SELECT S1.[DATE], S1.SHIFT, S1.DTHOURS, S1.OPHOURS, S1.THICK, S1.CODE, > S1.WIDTH, S1.LENGTH, S1.INPCS, S1.CSPCS, S1.RSPCS, S1.SHPCS, S1.RJPCS, 0 As > [Lot #], 0 As Blows, 0 As RejBrokenPanels, 0 As ShpBrokenPanels, 0 As > Fissures, 0 As SanderKO, 0 As Pits, 0 As SandThrough, 0 As SanderSetUp, 0 As > Spots, 0 As RejThkVar, 0 As ShpThkVar, 0 As Warp, 0 As ShpMisc, 0 As RejMisc > FROM [SANDER TABLE] As S1 LEFT JOIN TblDownGrades As S2 > ON S1.[DATE] = S2.SandDate AND S1.SHIFT = S2.SandShift AND S1.THICK = > S2.Thick AND S1.WIDTH = S2.Width AND S1.LENGTH = S2.Length AND S1.CSPCS = > S2.CriticalSurface AND S1.RSPCS = S2.RegularStock AND S1.SHPCS = > S2.[B-Grade] AND S1.RJPCS = S2.Reject > WHERE S2.ID IS NULL > UNION ALL > SELECT S1.[DATE], S1.SHIFT, S1.DTHOURS, S1.OPHOURS, S1.THICK, S1.CODE, > S1.WIDTH, S1.LENGTH, S1.INPCS, S1.CSPCS, S1.RSPCS, S1.SHPCS, S1.RJPCS, > S2.[Lot #], S2.Blows, S2.RejBrokenPanels, S2.ShpBrokenPanels, S2.Fissures, > S2.SanderKO, S2.Pits, S2.SandThrough, S2.SanderSetUp, S2.Spots, > S2.RejThkVar, S2.ShpThkVar, S2.Warp, S2.ShpMisc, S2.RejMisc > FROM [SANDER TABLE] As S1 INNER JOIN TblDownGrades As S2 > ON S1.[DATE] = S2.SandDate AND S1.SHIFT = S2.SandShift AND S1.THICK = > S2.Thick AND S1.WIDTH = S2.Width AND S1.LENGTH = S2.Length AND S1.CSPCS = > S2.CriticalSurface AND S1.RSPCS = S2.RegularStock AND S1.SHPCS = > S2.[B-Grade] AND S1.RJPCS = S2.Reject > UNION ALL > SELECT S2.SandDate, S2.SandShift, 0 As DTHOURS, 0 AS OPHOURS, S2.THICK, 0 As > CODE, S2.WIDTH, S2.LENGTH, 0 AS INPCS, S2.CriticalSurface, S2.RegularSTock, > S2.[B-Grade], S2.Reject, S2.[Lot #], S2.Blows, S2.RejBrokenPanels, > S2.ShpBrokenPanels, S2.Fissures, S2.SanderKO, S2.Pits, S2.SandThrough, > S2.SanderSetUp, S2.Spots, S2.RejThkVar, S2.ShpThkVar, S2.Warp, S2.ShpMisc, > S2.RejMisc > FROM [SANDER TABLE] As S1 RIGHT JOIN TblDownGrades As S2 > ON S1.[DATE] = S2.SandDate AND S1.SHIFT = S2.SandShift AND S1.THICK = > S2.Thick AND S1.WIDTH = S2.Width AND S1.LENGTH = S2.Length AND S1.CSPCS = > S2.CriticalSurface AND S1.RSPCS = S2.RegularStock AND S1.SHPCS = > S2.[B-Grade] AND S1.RJPCS = S2.Reject > WHERE S1.ID IS NULL; > > If you run the above query, it should produce the "merged" result you want. > Save the query and give it a name. Now create a new query on this Union > query. Change it into an Append query and tell it to append to the new > table you created above. Drag and drop all the fields onto the grid, and it > should fill in the correct field names on the Append To line. Run your > query to save the merged result in your new table. > > John Viescas, author > "Building Microsoft Access Applications" > "Microsoft Office Access 2003 Inside Out" > "Running Microsoft Access 2000" > "SQL Queries for Mere Mortals" > http://www.viescas.com/ > > > > > -----Original Message----- > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of Greg Saucier > > Sent: Wednesday, May 04, 2005 7:03 AM > > To: [email protected] > > Subject: [ms_access] Re: Merge Tables & Data? > > > > > > See below... > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> wrote: > > > Greg- > > > > > > How do I match a row from tblDownGrades with Sander? On ID? On > > > Date/Shift/Thick/Width/Length/CSPCS, etc? > > > > Match would be on all the common fields I indicated by numericall > > match... Date/Shift/Thick/Width/Length/CSPCS, etc not by ID. > > ID is an Autonumber with correlation to the other data. > > > > > > > If the match is on ID, which set of the nine common fields takes > > >precedence? > > > > All matching fields have EQUAL precedence. > > > > > If the match is on the 9 fields, do you care which ID is used? > > Or generate a new ID? > > > > Generate a new ID in the process would be fine. > > > > > And finally, > > > which table has more rows, and what do you want to do with rows > > >that don't match in either table? > > > > SANDER TABLE has 23,109 records > > tblDowngrades has 3,317 records > > > > We want to add tblDowngrades records (and missing fields) > > into SANDER table. > > > > > > > > > > John Viescas, author > > > "Building Microsoft Access Applications" > > > "Microsoft Office Access 2003 Inside Out" > > > "Running Microsoft Access 2000" > > > "SQL Queries for Mere Mortals" > > > http://www.viescas.com/ > > > > > > > -----Original Message----- > > > > From: [email protected] > > > > [mailto:[EMAIL PROTECTED] On Behalf Of Greg Saucier > > > > Sent: Tuesday, May 03, 2005 3:08 PM > > > > To: [email protected] > > > > Subject: [ms_access] Re: Merge Tables & Data? > > > > > > > > > > > > John, > > > > > > > > Here are the actual tables & fields. The ones with ASTERISK > > > > are common fields to both. What we want is to bring data > > > > from TblDowngrades inot SANDER table. They are numerically > > matched > > > > so you can see which ones are "common". Data type is NUMBER > > > > on all except the Date & ID. > > > > > > > > > > > > SANDER TABLE > > > > > > > > ID > > > > 1-DATE > > > > 2-SHIFT > > > > DTHOURS > > > > OPHOURS > > > > 3-THICK > > > > CODE > > > > 4-WIDTH > > > > 5-LENGTH > > > > INPCS > > > > 6-CSPCS > > > > 7-RSPCS > > > > 8-SHPCS > > > > 9-RJPCS > > > > > > > > > > > > TblDowngrades TABLE > > > > > > > > ID > > > > 1-SandDate > > > > 2-SandShift > > > > Lot # > > > > 3-Thick > > > > 4-Width > > > > 5-Length > > > > 6-CriticalSurface > > > > 7-RegularStock > > > > 8-B-Grade > > > > 9-Reject > > > > Blows > > > > RejBrokenPanels > > > > ShpBrokenPanels > > > > Fissures > > > > SanderKO > > > > Pits > > > > SandThrough > > > > SanderSetUp > > > > Spots > > > > RejThkVar > > > > ShpThkVar > > > > Warp > > > > ShpMisc > > > > RejMisc > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > wrote: > > > > > Greg- > > > > > > > > > > You still haven't told me what's *really* in these tables. You > > > > have implied > > > > > that the rows match on "Y" values, but now you tell me there > > are > > > > multiple > > > > > "Y's". I can come up with a simple solution if there's a > > unique > > > > way to > > > > > match the rows in the two tables. On ID? On some set of > > columns? > > > > Need > > > > > more info... > > > > > > > > > > John Viescas, author > > > > > "Building Microsoft Access Applications" > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > "Running Microsoft Access 2000" > > > > > "SQL Queries for Mere Mortals" > > > > > http://www.viescas.com/ > > > > > > > > > > > -----Original Message----- > > > > > > From: [email protected] > > > > > > [mailto:[EMAIL PROTECTED] On Behalf Of Greg Saucier > > > > > > Sent: Tuesday, May 03, 2005 2:32 PM > > > > > > To: [email protected] > > > > > > Subject: [ms_access] Re: Merge Tables & Data? > > > > > > > > > > > > > > > > > > > > > > > > In reality there are many "Y"'s, multiple fields that are > > common > > > > > > to both tables. Then in the case of the fields that are not > > in > > > > > > the one we're importing to we want to bring in those fields > > too. > > > > > > > > > > > > I guess there is no "merge" table function anywhere? Maybe > > > > > > we can start there. Or a work around for such? > > > > > > > > > > > > Thanks, > > > > > > Greg > > > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" <[EMAIL PROTECTED]> > > > > wrote: > > > > > > > Ah, but the common field is "Y", and it isn't necessarily > > > > unique? > > > > > > Please > > > > > > > see my earlier response and answer the questions I posed > > there. > > > > > > > > > > > > > > John Viescas, author > > > > > > > "Building Microsoft Access Applications" > > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > > "Running Microsoft Access 2000" > > > > > > > "SQL Queries for Mere Mortals" > > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > From: [email protected] > > > > > > > > [mailto:[EMAIL PROTECTED] On Behalf Of Greg > > Saucier > > > > > > > > Sent: Tuesday, May 03, 2005 1:47 PM > > > > > > > > To: [email protected] > > > > > > > > Subject: [ms_access] Re: Merge Tables & Data? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > I'm sorry John, yes there is a field in both tables > > > > called "ID" > > > > > > in > > > > > > > > Autonumber format that is set as primary key. > > > > > > > > > > > > > > > > Greg > > > > > > > > > > > > > > > > > > > > > > > > --- In [email protected], "Greg Saucier" > > > > <[EMAIL PROTECTED]> > > > > > > wrote: > > > > > > > > > > > > > > > > > > No, there is no primary key in either table, just some > > > > > > > > > common fields to both tables. > > > > > > > > > > > > > > > > > > Greg > > > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" > > > > <[EMAIL PROTECTED]> > > > > > > wrote: > > > > > > > > > > Greg- > > > > > > > > > > > > > > > > > > > > Does either table have a Primary Key? Field Y? > > > > > > > > > > > > > > > > > > > > John Viescas, author > > > > > > > > > > "Building Microsoft Access Applications" > > > > > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > > > > > "Running Microsoft Access 2000" > > > > > > > > > > "SQL Queries for Mere Mortals" > > > > > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > > > > From: [email protected] > > > > > > > > > > > [mailto:[EMAIL PROTECTED] On Behalf Of > > Greg > > > > Saucier > > > > > > > > > > > Sent: Tuesday, May 03, 2005 11:50 AM > > > > > > > > > > > To: [email protected] > > > > > > > > > > > Subject: [ms_access] Re: Merge Tables & Data? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hi John, > > > > > > > > > > > > > > > > > > > > > > Thanks so much for the help. > > > > > > > > > > > > > > > > > > > > > > Yes the two tables have duplicate data, but not in > > > > every > > > > > > field. > > > > > > > > > > > There are field differences in the two tables and I > > am > > > > > > trying > > > > > > > > to > > > > > > > > > > > merge them together. > > > > > > > > > > > > > > > > > > > > > > In Table A for example I have field X & Y but not Z. > > > > > > > > > > > I guess we are wanting to reconcile the records in > > each > > > > > > table > > > > > > > > > > > and merge them into one, thus eliminating the dups > > and > > > > ALSO > > > > > > > > > > > bringing the field data over that is not there now. > > > > > > > > > > > > > > > > > > > > > > If you could imagine a way to bring in an entire > > FIELD > > > > from > > > > > > > > > > > one table into another that is what we are trying > > to > > > > > > achieve. > > > > > > > > > > > > > > > > > > > > > > Hope that helps. > > > > > > > > > > > > > > > > > > > > > > Greg > > > > > > > > > > > > > > > > > > > > > > --- In [email protected], "John Viescas" > > > > > > <[EMAIL PROTECTED]> > > > > > > > > > wrote: > > > > > > > > > > > > Greg- > > > > > > > > > > > > > > > > > > > > > > > > Please explain what you mean by "there are > > > > duplicates." > > > > > > Do > > > > > > > > you > > > > > > > > > > > want to > > > > > > > > > > > > eliminate the duplicates? Also are there rows in > > > > Table A > > > > > > or > > > > > > > > > Table > > > > > > > > > > > B that do > > > > > > > > > > > > not have a matching Y value in the other table? > > > > > > > > > > > > > > > > > > > > > > > > Answer these questions, and I can write the > > > > appropriate > > > > > > SQL > > > > > > > > for > > > > > > > > > you > > > > > > > > > > > to get > > > > > > > > > > > > the job done. > > > > > > > > > > > > > > > > > > > > > > > > John Viescas, author > > > > > > > > > > > > "Building Microsoft Access Applications" > > > > > > > > > > > > "Microsoft Office Access 2003 Inside Out" > > > > > > > > > > > > "Running Microsoft Access 2000" > > > > > > > > > > > > "SQL Queries for Mere Mortals" > > > > > > > > > > > > http://www.viescas.com/ > > > > > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > > > > > > From: [email protected] > > > > > > > > > > > > > [mailto:[EMAIL PROTECTED] On Behalf Of > > > > Greg > > > > > > Saucier > > > > > > > > > > > > > Sent: Tuesday, May 03, 2005 9:04 AM > > > > > > > > > > > > > To: [email protected] > > > > > > > > > > > > > Subject: [ms_access] Merge Tables & Data? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > > > > > > > > > > > > > Can someone help me figure out if its possible > > to > > > > > > > > > > > > > merge two tables together into one while > > maintaining > > > > > > > > > > > > > existing data? > > > > > > > > > > > > > > > > > > > > > > > > > > For example, Table A has fields X & Y and Table > > B > > > > > > > > > > > > > has fields Y & Z. I want to make a new table > > (or > > > > > > query) > > > > > > > > with > > > > > > > > > > > > > X, Y & Z and have all the records match (there > > are > > > > > > > > > > > > > duplicates) when I am done. > > > > > > > > > > > > > > > > > > > > > > > > > > Thanks > > > > > > > > > > > > > Greg > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Groups Links > > > > > > > > > > Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> 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/
