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/
