workaround for non normalized table

I've inherited a database that relies heavily on a non normalized table.  
Until I can convince the general manager that I can normalize the data 
without losing any records, I've got to have a workaround for certain 
situations.  Namely, I need to ensure that data is not being badly reproduced 
at various stages of our operations.  I would therefore like to reference the 
information directly from the main table in order to populate certain 
information in related tables.  In this case, the main table uses [Order 
Number] as its primary key, and ties it to a bunch of information like 
material type, alloy, form, etc., but there are up to four part numbers for 
every order number.  When we process these orders, these part numbers can get 
separated and so we will have multiple parts from the same order in various 
stages of production throughout the shop.  I need a query that will return 
all of the part numbers for every order number in a common field.  In other 
words, in its simplest form the query would have two fields, Order Number and 
Part Number, and for every Order Number would produce four records in the 
Part Number field.  I'm not worried about null records at this time.  Not 
being an adept in SQL, I thought I'd ask the big brains here at the 
newsgroup.  Your help will be appreciated!
-- 
Why are you asking me?  I dont know what Im doing!

Jaybird
0
Utf
5/30/2007 2:33:01 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
569 Views

Similar Articles

[PageSpeed] 45

Sounds like a good place to use a union query as pseudo table.  The problem 
with a UNION query is that no indexes will be available when you use the 
Union query in other queries and any query where you do use it is probably 
(almost definitely) not going to be updatable.

SELECT [Order number], PartNo1 as PartNo
FROM [MainTable]
WHERE PartNo1 is Not Null
UNION ALL
SELECT [Order number], PartNo2
FROM [MainTable]
WHERE PartNo2 is Not Null
UNION ALL
SELECT [Order number], PartNo3
FROM [MainTable]
WHERE PartNo3 is Not Null
UNION ALL
SELECT [Order number], PartNo4
FROM [MainTable]
WHERE PartNo4 is Not Null

Save that query as qOrderParts and use it in your other queries.  OR you 
could build a table with the relevant structure and use the Union query as 
the source of an append query.  The problem with that is that the data in 
the new table will almost never be in synch with the actual data and you 
will have to delete all the records from the table before you do an append.

As you've mentioned the real solution is to redesign the table structure.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jaybird" <Jaybird@discussions.microsoft.com> wrote in message 
news:40CE02A9-65B1-4922-8448-EB2B9EDBFEA3@microsoft.com...
> I've inherited a database that relies heavily on a non normalized table.
> Until I can convince the general manager that I can normalize the data
> without losing any records, I've got to have a workaround for certain
> situations.  Namely, I need to ensure that data is not being badly 
> reproduced
> at various stages of our operations.  I would therefore like to reference 
> the
> information directly from the main table in order to populate certain
> information in related tables.  In this case, the main table uses [Order
> Number] as its primary key, and ties it to a bunch of information like
> material type, alloy, form, etc., but there are up to four part numbers 
> for
> every order number.  When we process these orders, these part numbers can 
> get
> separated and so we will have multiple parts from the same order in 
> various
> stages of production throughout the shop.  I need a query that will return
> all of the part numbers for every order number in a common field.  In 
> other
> words, in its simplest form the query would have two fields, Order Number 
> and
> Part Number, and for every Order Number would produce four records in the
> Part Number field.  I'm not worried about null records at this time.  Not
> being an adept in SQL, I thought I'd ask the big brains here at the
> newsgroup.  Your help will be appreciated!
> -- 
> Why are you asking me?  I dont know what Im doing!
>
> Jaybird 


0
John
5/30/2007 3:38:18 PM
BINGO!  Thank you for being so specific with your syntax!  You have saved me 
a huge amount of time...  The only changes I made where to ORDER BY [Order 
Number] and to set all four part numbers as [Part Number] as you did for 
PartNo1.  It even filters out the nulls!  I love it!  I'm guessing that when 
the time comes to normalize this table that I would use a method similar to 
this in a Make-Table Query?  Have you any suggestions for ensuring and double 
checking that I don't lose any data?
-- 
Why are you asking me?  I dont know what Im doing!

Jaybird


"John Spencer" wrote:

> Sounds like a good place to use a union query as pseudo table.  The problem 
> with a UNION query is that no indexes will be available when you use the 
> Union query in other queries and any query where you do use it is probably 
> (almost definitely) not going to be updatable.
> 
> SELECT [Order number], PartNo1 as PartNo
> FROM [MainTable]
> WHERE PartNo1 is Not Null
> UNION ALL
> SELECT [Order number], PartNo2
> FROM [MainTable]
> WHERE PartNo2 is Not Null
> UNION ALL
> SELECT [Order number], PartNo3
> FROM [MainTable]
> WHERE PartNo3 is Not Null
> UNION ALL
> SELECT [Order number], PartNo4
> FROM [MainTable]
> WHERE PartNo4 is Not Null
> 
> Save that query as qOrderParts and use it in your other queries.  OR you 
> could build a table with the relevant structure and use the Union query as 
> the source of an append query.  The problem with that is that the data in 
> the new table will almost never be in synch with the actual data and you 
> will have to delete all the records from the table before you do an append.
> 
> As you've mentioned the real solution is to redesign the table structure.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message 
> news:40CE02A9-65B1-4922-8448-EB2B9EDBFEA3@microsoft.com...
> > I've inherited a database that relies heavily on a non normalized table.
> > Until I can convince the general manager that I can normalize the data
> > without losing any records, I've got to have a workaround for certain
> > situations.  Namely, I need to ensure that data is not being badly 
> > reproduced
> > at various stages of our operations.  I would therefore like to reference 
> > the
> > information directly from the main table in order to populate certain
> > information in related tables.  In this case, the main table uses [Order
> > Number] as its primary key, and ties it to a bunch of information like
> > material type, alloy, form, etc., but there are up to four part numbers 
> > for
> > every order number.  When we process these orders, these part numbers can 
> > get
> > separated and so we will have multiple parts from the same order in 
> > various
> > stages of production throughout the shop.  I need a query that will return
> > all of the part numbers for every order number in a common field.  In 
> > other
> > words, in its simplest form the query would have two fields, Order Number 
> > and
> > Part Number, and for every Order Number would produce four records in the
> > Part Number field.  I'm not worried about null records at this time.  Not
> > being an adept in SQL, I thought I'd ask the big brains here at the
> > newsgroup.  Your help will be appreciated!
> > -- 
> > Why are you asking me?  I dont know what Im doing!
> >
> > Jaybird 
> 
> 
> 
0
Utf
5/30/2007 4:13:00 PM
Yes, I would use the above method.

Also, you might consider one small modification.  IF you have any records 
with the part no being repeated in two (or more fields) you can change the 
UNION ALL to just UNION.  That will eliminate duplicates (if that is what 
you want).

When it comes time to normalize, I would build the table with the fields I 
wanted and then use the Union query in an append query to add the records to 
the normalized tabl

IF you wanted you could use an unmatched query against the Order Number and 
Part1 and then against Order Number and Part2., etc.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jaybird" <Jaybird@discussions.microsoft.com> wrote in message 
news:038DCB14-4775-4691-8A47-A7D0A61CB7E2@microsoft.com...
> BINGO!  Thank you for being so specific with your syntax!  You have saved 
> me
> a huge amount of time...  The only changes I made where to ORDER BY [Order
> Number] and to set all four part numbers as [Part Number] as you did for
> PartNo1.  It even filters out the nulls!  I love it!  I'm guessing that 
> when
> the time comes to normalize this table that I would use a method similar 
> to
> this in a Make-Table Query?  Have you any suggestions for ensuring and 
> double
> checking that I don't lose any data?
> -- 
> Why are you asking me?  I dont know what Im doing!
>
> Jaybird
>
>
> "John Spencer" wrote:
>
>> Sounds like a good place to use a union query as pseudo table.  The 
>> problem
>> with a UNION query is that no indexes will be available when you use the
>> Union query in other queries and any query where you do use it is 
>> probably
>> (almost definitely) not going to be updatable.
>>
>> SELECT [Order number], PartNo1 as PartNo
>> FROM [MainTable]
>> WHERE PartNo1 is Not Null
>> UNION ALL
>> SELECT [Order number], PartNo2
>> FROM [MainTable]
>> WHERE PartNo2 is Not Null
>> UNION ALL
>> SELECT [Order number], PartNo3
>> FROM [MainTable]
>> WHERE PartNo3 is Not Null
>> UNION ALL
>> SELECT [Order number], PartNo4
>> FROM [MainTable]
>> WHERE PartNo4 is Not Null
>>
>> Save that query as qOrderParts and use it in your other queries.  OR you
>> could build a table with the relevant structure and use the Union query 
>> as
>> the source of an append query.  The problem with that is that the data in
>> the new table will almost never be in synch with the actual data and you
>> will have to delete all the records from the table before you do an 
>> append.
>>
>> As you've mentioned the real solution is to redesign the table structure.
>>
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message
>> news:40CE02A9-65B1-4922-8448-EB2B9EDBFEA3@microsoft.com...
>> > I've inherited a database that relies heavily on a non normalized 
>> > table.
>> > Until I can convince the general manager that I can normalize the data
>> > without losing any records, I've got to have a workaround for certain
>> > situations.  Namely, I need to ensure that data is not being badly
>> > reproduced
>> > at various stages of our operations.  I would therefore like to 
>> > reference
>> > the
>> > information directly from the main table in order to populate certain
>> > information in related tables.  In this case, the main table uses 
>> > [Order
>> > Number] as its primary key, and ties it to a bunch of information like
>> > material type, alloy, form, etc., but there are up to four part numbers
>> > for
>> > every order number.  When we process these orders, these part numbers 
>> > can
>> > get
>> > separated and so we will have multiple parts from the same order in
>> > various
>> > stages of production throughout the shop.  I need a query that will 
>> > return
>> > all of the part numbers for every order number in a common field.  In
>> > other
>> > words, in its simplest form the query would have two fields, Order 
>> > Number
>> > and
>> > Part Number, and for every Order Number would produce four records in 
>> > the
>> > Part Number field.  I'm not worried about null records at this time. 
>> > Not
>> > being an adept in SQL, I thought I'd ask the big brains here at the
>> > newsgroup.  Your help will be appreciated!
>> > -- 
>> > Why are you asking me?  I dont know what Im doing!
>> >
>> > Jaybird
>>
>>
>> 


0
John
5/30/2007 6:05:28 PM
Thank You!  These are all excellent suggestions.  You have saved me much 
hand-wringing!  I will do it!
-- 
Why are you asking me?  I dont know what Im doing!

Jaybird


"John Spencer" wrote:

> Yes, I would use the above method.
> 
> Also, you might consider one small modification.  IF you have any records 
> with the part no being repeated in two (or more fields) you can change the 
> UNION ALL to just UNION.  That will eliminate duplicates (if that is what 
> you want).
> 
> When it comes time to normalize, I would build the table with the fields I 
> wanted and then use the Union query in an append query to add the records to 
> the normalized tabl
> 
> IF you wanted you could use an unmatched query against the Order Number and 
> Part1 and then against Order Number and Part2., etc.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message 
> news:038DCB14-4775-4691-8A47-A7D0A61CB7E2@microsoft.com...
> > BINGO!  Thank you for being so specific with your syntax!  You have saved 
> > me
> > a huge amount of time...  The only changes I made where to ORDER BY [Order
> > Number] and to set all four part numbers as [Part Number] as you did for
> > PartNo1.  It even filters out the nulls!  I love it!  I'm guessing that 
> > when
> > the time comes to normalize this table that I would use a method similar 
> > to
> > this in a Make-Table Query?  Have you any suggestions for ensuring and 
> > double
> > checking that I don't lose any data?
> > -- 
> > Why are you asking me?  I dont know what Im doing!
> >
> > Jaybird
> >
> >
> > "John Spencer" wrote:
> >
> >> Sounds like a good place to use a union query as pseudo table.  The 
> >> problem
> >> with a UNION query is that no indexes will be available when you use the
> >> Union query in other queries and any query where you do use it is 
> >> probably
> >> (almost definitely) not going to be updatable.
> >>
> >> SELECT [Order number], PartNo1 as PartNo
> >> FROM [MainTable]
> >> WHERE PartNo1 is Not Null
> >> UNION ALL
> >> SELECT [Order number], PartNo2
> >> FROM [MainTable]
> >> WHERE PartNo2 is Not Null
> >> UNION ALL
> >> SELECT [Order number], PartNo3
> >> FROM [MainTable]
> >> WHERE PartNo3 is Not Null
> >> UNION ALL
> >> SELECT [Order number], PartNo4
> >> FROM [MainTable]
> >> WHERE PartNo4 is Not Null
> >>
> >> Save that query as qOrderParts and use it in your other queries.  OR you
> >> could build a table with the relevant structure and use the Union query 
> >> as
> >> the source of an append query.  The problem with that is that the data in
> >> the new table will almost never be in synch with the actual data and you
> >> will have to delete all the records from the table before you do an 
> >> append.
> >>
> >> As you've mentioned the real solution is to redesign the table structure.
> >>
> >> -- 
> >> John Spencer
> >> Access MVP 2002-2005, 2007
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message
> >> news:40CE02A9-65B1-4922-8448-EB2B9EDBFEA3@microsoft.com...
> >> > I've inherited a database that relies heavily on a non normalized 
> >> > table.
> >> > Until I can convince the general manager that I can normalize the data
> >> > without losing any records, I've got to have a workaround for certain
> >> > situations.  Namely, I need to ensure that data is not being badly
> >> > reproduced
> >> > at various stages of our operations.  I would therefore like to 
> >> > reference
> >> > the
> >> > information directly from the main table in order to populate certain
> >> > information in related tables.  In this case, the main table uses 
> >> > [Order
> >> > Number] as its primary key, and ties it to a bunch of information like
> >> > material type, alloy, form, etc., but there are up to four part numbers
> >> > for
> >> > every order number.  When we process these orders, these part numbers 
> >> > can
> >> > get
> >> > separated and so we will have multiple parts from the same order in
> >> > various
> >> > stages of production throughout the shop.  I need a query that will 
> >> > return
> >> > all of the part numbers for every order number in a common field.  In
> >> > other
> >> > words, in its simplest form the query would have two fields, Order 
> >> > Number
> >> > and
> >> > Part Number, and for every Order Number would produce four records in 
> >> > the
> >> > Part Number field.  I'm not worried about null records at this time. 
> >> > Not
> >> > being an adept in SQL, I thought I'd ask the big brains here at the
> >> > newsgroup.  Your help will be appreciated!
> >> > -- 
> >> > Why are you asking me?  I dont know what Im doing!
> >> >
> >> > Jaybird
> >>
> >>
> >> 
> 
> 
> 
0
Utf
5/30/2007 6:27:00 PM
A small problem...  I've modified the query you gave me to include several 
other fields on a form.  I'm trying to populate these fields by selecting a 
specific instance of the [Order Number] that is produced by the query.  
However, only the values of the FIRST instance of {Order Number] are 
populating these fields, no matter which one I select.  (I'm using the UNION 
query as my row source for a combo box.)  How do I specify the data to be 
displayed?

---Why are you asking me?  I dont know what Im doing!

Jaybird


"Jaybird" wrote:

> Thank You!  These are all excellent suggestions.  You have saved me much 
> hand-wringing!  I will do it!
> -- 
> Why are you asking me?  I dont know what Im doing!
> 
> Jaybird
> 
> 
> "John Spencer" wrote:
> 
> > Yes, I would use the above method.
> > 
> > Also, you might consider one small modification.  IF you have any records 
> > with the part no being repeated in two (or more fields) you can change the 
> > UNION ALL to just UNION.  That will eliminate duplicates (if that is what 
> > you want).
> > 
> > When it comes time to normalize, I would build the table with the fields I 
> > wanted and then use the Union query in an append query to add the records to 
> > the normalized tabl
> > 
> > IF you wanted you could use an unmatched query against the Order Number and 
> > Part1 and then against Order Number and Part2., etc.
> > 
> > -- 
> > John Spencer
> > Access MVP 2002-2005, 2007
> > Center for Health Program Development and Management
> > University of Maryland Baltimore County
> > ..
> > 
> > "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message 
> > news:038DCB14-4775-4691-8A47-A7D0A61CB7E2@microsoft.com...
> > > BINGO!  Thank you for being so specific with your syntax!  You have saved 
> > > me
> > > a huge amount of time...  The only changes I made where to ORDER BY [Order
> > > Number] and to set all four part numbers as [Part Number] as you did for
> > > PartNo1.  It even filters out the nulls!  I love it!  I'm guessing that 
> > > when
> > > the time comes to normalize this table that I would use a method similar 
> > > to
> > > this in a Make-Table Query?  Have you any suggestions for ensuring and 
> > > double
> > > checking that I don't lose any data?
> > > -- 
> > > Why are you asking me?  I dont know what Im doing!
> > >
> > > Jaybird
> > >
> > >
> > > "John Spencer" wrote:
> > >
> > >> Sounds like a good place to use a union query as pseudo table.  The 
> > >> problem
> > >> with a UNION query is that no indexes will be available when you use the
> > >> Union query in other queries and any query where you do use it is 
> > >> probably
> > >> (almost definitely) not going to be updatable.
> > >>
> > >> SELECT [Order number], PartNo1 as PartNo
> > >> FROM [MainTable]
> > >> WHERE PartNo1 is Not Null
> > >> UNION ALL
> > >> SELECT [Order number], PartNo2
> > >> FROM [MainTable]
> > >> WHERE PartNo2 is Not Null
> > >> UNION ALL
> > >> SELECT [Order number], PartNo3
> > >> FROM [MainTable]
> > >> WHERE PartNo3 is Not Null
> > >> UNION ALL
> > >> SELECT [Order number], PartNo4
> > >> FROM [MainTable]
> > >> WHERE PartNo4 is Not Null
> > >>
> > >> Save that query as qOrderParts and use it in your other queries.  OR you
> > >> could build a table with the relevant structure and use the Union query 
> > >> as
> > >> the source of an append query.  The problem with that is that the data in
> > >> the new table will almost never be in synch with the actual data and you
> > >> will have to delete all the records from the table before you do an 
> > >> append.
> > >>
> > >> As you've mentioned the real solution is to redesign the table structure.
> > >>
> > >> -- 
> > >> John Spencer
> > >> Access MVP 2002-2005, 2007
> > >> Center for Health Program Development and Management
> > >> University of Maryland Baltimore County
> > >> ..
> > >>
> > >> "Jaybird" <Jaybird@discussions.microsoft.com> wrote in message
> > >> news:40CE02A9-65B1-4922-8448-EB2B9EDBFEA3@microsoft.com...
> > >> > I've inherited a database that relies heavily on a non normalized 
> > >> > table.
> > >> > Until I can convince the general manager that I can normalize the data
> > >> > without losing any records, I've got to have a workaround for certain
> > >> > situations.  Namely, I need to ensure that data is not being badly
> > >> > reproduced
> > >> > at various stages of our operations.  I would therefore like to 
> > >> > reference
> > >> > the
> > >> > information directly from the main table in order to populate certain
> > >> > information in related tables.  In this case, the main table uses 
> > >> > [Order
> > >> > Number] as its primary key, and ties it to a bunch of information like
> > >> > material type, alloy, form, etc., but there are up to four part numbers
> > >> > for
> > >> > every order number.  When we process these orders, these part numbers 
> > >> > can
> > >> > get
> > >> > separated and so we will have multiple parts from the same order in
> > >> > various
> > >> > stages of production throughout the shop.  I need a query that will 
> > >> > return
> > >> > all of the part numbers for every order number in a common field.  In
> > >> > other
> > >> > words, in its simplest form the query would have two fields, Order 
> > >> > Number
> > >> > and
> > >> > Part Number, and for every Order Number would produce four records in 
> > >> > the
> > >> > Part Number field.  I'm not worried about null records at this time. 
> > >> > Not
> > >> > being an adept in SQL, I thought I'd ask the big brains here at the
> > >> > newsgroup.  Your help will be appreciated!
> > >> > -- 
> > >> > Why are you asking me?  I dont know what Im doing!
> > >> >
> > >> > Jaybird
> > >>
> > >>
> > >> 
> > 
> > 
> > 
0
Utf
5/30/2007 7:00:00 PM
Reply:

Similar Artilces:

Link Access table to Excel worksheet
I want to link these so that they can be edited in either programme. Whichever way, the link box is greyed out. You can make a linked table in Access. It's File - Get External Data - Link. The data lives in the Excel workbook, I think (I don't think Access keeps a copy of it). But you can change data in either Excel or Access. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "dalesrunner" <dalesrunner@discussions.microsoft.com> wrote in message news:A165D6C6-D173-45F7-94D5-352E3AA5121E@microsoft.com... ...

non-domain PC on the network
Will I always see so many failed security audits in this kind of situation? This computers are only using the internet connection and no server resources. I'm surprised to see any logs relating to them. yaro ...

Add non-inventory item in MO Reciept entry (Unit Cost)
Dear All, I am using the (Add Component) button on the MO Reciept entry screen to add some non-inventory items, I notice that the decimal places for the unit cost is (2) although my Functional currency is (3). Kindly advice for the reason behind this, or if there is any setup required for that. Thanks Dear, Go to Tools --> Setup --> Purchasing --> Purchase Order Processing Find Decimal Places for Non-inventoried Items, click on the Expantion Button near to the Currency Field and set the decimal places for you curreny. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS S...

Table of Conents Out of Order`
Using Word 2007 and I have created a Table of Contents within a frame (working on a newsletter). The TOC is picking up its data from styles and it works perfectly, except the items within the table of contents are out of order. For example, I have two articles on page 1 and the second article title is showing first in the table of contents. Suggestions? As always, thank you for any help you can provide. If the articles are in text boxes, it could be that the "second" article is anchored to a paragraph that actually precedes the paragraph to which the "first&q...

Webstore / exchnage Table
I want to import my XML order document into the database table "Exchange" set the ProcessorCode field to "YahooStore". Due to my limited expierence with XML I can not get the file into the Exchange table. Is there a specific SP, DTS or VB script that does this? Also, if I have multiple orders in one file do I need to strip them out and insert into the exchange table as individual orders? Your assistance would be much appreciated. Cliff ...

Copy html table to word not keeping settings
I want to copy a html table to a word programatically. I have manage to get to a point where the table is pasted. But the paste is not the same as if i would use Ctrl+V or Edit/Paste. For merged cells the font settings are not kept. Is there a way to keep the settings for merged cells? Or a way to paste the html table in a word like the Ctrl+C/Ctrl+V does? ...

Is it possible to get text values in a pivot table?
From this: ROW COL VAL A HD1 X B HD2 Y B HD1 Z C HD3 J A HD3 Z B HD3 R C HD1 S Get this? Var of VAL COL ROW HD1 HD2 HD3 A X Z B Z Y R C S J (If 2 rows go to same cell, would need to get maximum or first). Not with Pivot Table Let you A, B C data be A1:A7, the HDn data in B1:B7 and the other data in C1:C7 In some convenient place (I use A11) enter =A1&B1 and copy down the column (you could put this in another sheet or in a hidden column) Make the heading HD1, HD2 - I did this in F1:H1 and the row ...

copy a pivot table to another sheet in a macro
Hi there, I created a macro that will copy copy the information from a created pivot table to another sheet. I seemed ok when I recorded the macro. But there's no pivot table on the new sheet when I run the macro. What should I do to correct the problem? Thanks, Aline -- Aline Record another macro when you try it again? If it fails again, you may want to describe what you did to copy the data (copy the cells to a new sheet or copy the sheet???). And share the code (that fails) that does the copy. Aline wrote: > > Hi there, > > I created a...

Using a query versus a table for the look up wizard
Hello, I have a field which I would like to use the lookup wizard for to generate a drop down list for the user to select from. My question is whether my databse would run faster if I used: a query from an already established table as the values for the lookup wizard, or to build a new table to hold the information for the lookup wizard to use. I think you'll find few of us recommend the use of the Lookup Wizard. Lookup fields only have a place in tables, and your users should never be working directly with the tables. Create a form, and use a combo box on the form. (See http...

How do I print addresses in an Excel table on a flyer for mailing?
I have a one-sided flyer that will be tri-folded for mailing. Before folding, how do I print the addresses from an Excel table on the blank side of the flyer? And how do I include my return address? -- kateinmo Hi Kateinmo, Look here: http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "kateinmo" <kateinmo@discussions.microsoft.com> wrote in message news:8542D8A7-0132-42DF-933A-5E1B7780CF4D@microsoft.com... |I have a one-sided flyer that will be tri-folded for mailing. Before folding, | how do I print the addresses fr...

In Excel 2000, How can I change the order in a pivot table?
I am having problems changing the order of my pivot table. The "Drop Column Fields here" is date related and I grouped that area by month. I can change the chart data series order, save the file, close the file, then reopen it and the changes go back to the default view before the series order in the chart changed. I have another co-worker that has Office 2003 and it does not happen to her. It retains the information as she saved it. Is it a glitch in Excel 2000? ...

Table Filters and Empty Records
Hi, I'm quite new to Reporting Services and am having a bit of a confusing afternoon with it. I'm trying to build a report that contains a series of discrete reports for a number of projects that show project data and any milestone tasks associated with each project; if a project has no milesetones, the project data is still to be displayed. So, I build a report that has a series of fields showing project information (name, cost, state, etc.) and then a table below it to contain the milestone tasks for each project. All of these are placed within a list data region ...

graphing ordered non-numeric value
How does one graph an ordered but non-numeric value (e.g. Excellent, Very Good, Good, Satisfactory, Fair, Poor, Failed) on the Y-axis and the date on the x-axis? Hi Mkchu, > How does one graph an ordered but non-numeric value (e.g. Excellent, Very > Good, Good, Satisfactory, Fair, Poor, Failed) on the Y-axis and the date on > the x-axis? Probably the easiest way is to give each of them a number (e.g. Failed = 0, Excellent = 6) and plot those. Then format the Y axis to show markers but no labels and use text boxes for the labels. Regards Stephen Bullen Microsoft MVP - Excel...

Display table name as field in query
Does anyone know if there is a way to display the table name as a field in a query? I have a UNION query that merges all of the records from 10 different tables, but I need a field that indicates which table each record comes from. Any help would be greatly appreciated. Thanks, Craig Craig wrote: >Does anyone know if there is a way to display the table name as a field in a >query? > >I have a UNION query that merges all of the records from 10 different >tables, but I need a field that indicates which table each record comes from. Just use the table name in a calcula...

pivot table with dynamic base data
hiya, im trying to create a pivot table where I can change my base data (inserting or deleting rows of data) and still have this reflected in the pivot table. My solution was to use entire columns as my range for input to the pivotTable, but this means including blank rows which seems to muck up the calculations. I cant think how else i can allow for new rows to be added to the base data anyone got any ideas how to do this? thanks loads for your help! You can use a dynamic range as the source. There are instructions here: http://www.contextures.com/xlPivot01.html bb wrote: > hiy...

Ref Transfering Info from a table
I don't know if I'm asking the impossible here but I'll give it a try. I have a excel program which I am trying to set up a function whereby it will create a work rota based on a shift pattern. It currently will create a table with staff names on it and there shift pattern for a given week. What I am trying to do is let the user select shift pattern 1 for example and have excel transfer this onto the rota but place the name instead of shift in the appropriate shift. ie If I select shift 1 it will place name A onto the early shift and place A on the Day off line whi...

Need help finding non-duplicates email addresses...
Hi, I have a list of email addresses which I would like to find only the new email addresses within the list. Inside of my Excel worksheet I have a row of e-mail addresses. Some of the names are duplicated which tells me they already exist somewhere in the row. I wish to display only the new names. Can you tell me how this can be done in Excel? Thanks in advance for your assistance, Greg Hi, Select the row. GoTo conditional formatting, Select Formula-Is option in first combo, enter =COUNTIF(1:1,A1)>1 in second box. Select format and select suitable font,colour etc. Click OK. Every repe...

table sy_users error
Hi; I am new to GP and this client is using ver 5 on win 98. We are getting message on both pcs now that " an open operation on table sy_users_mstr failed because the path does not exist or ... main pc that handles gp has multiple directorys. GP seems to operate out of c:\acct\dynamics. Under utilitys I see where one can change which machine uses alias for c. Most of the pcs use a mapped drive g ( that goes to the main pc, directory acct (NOT all the way to dyamics) or f that goes to same pc, another directory not in my mind just now. thanks for any help. I am assumming that this i...

Selecting Maximum Values in a Query with joins to other tables
I'm having difficulty with a query and hope someone can help me out. Basically, I'm looking to select the maximum amounts from a table based on a column in one of the tables named StateID. It works fine when I do this: SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS Amount, dbo.States.StateName FROM dbo.Bids INNER JOIN dbo.States ON dbo.Bids.StateID = dbo.States.StateID GROUP BY dbo.Bids.StateID, dbo.States.StateName ORDER BY dbo.States.StateName However, when I start to join fields from other tables in...

Pivot Table #16
After I have created my Pivot Table, how do I find the "source data" from the Pivot Table i just created. Thanks in advance. DaveB Right click... go to pivot wizard... press back a couple times to get to the source data. >-----Original Message----- >After I have created my Pivot Table, how do I find >the "source data" from the Pivot Table i just created. >Thanks in advance. > >DaveB >. > ...

Pivot Table? #2
This is an example of my data Name ID Source A SA Amt a SA Amt b SA Amt c Source B SB Amt a SB Amt b SB Amt JAMES 23 1 40 500 1000 10 10 100 75 JOHN 24 10 0 800 1200 4 15 200 80 SUE 25 3 20 1000 700 1 20 300 95 BETTY 26 1 30 2000 2500 10 25 400 110 BOSS 27 3 5000 3000 4000 1 30 500 120 This is what I want it to look like Name ID Source Amt a Amt b Amt JAMES 23 1 40 500 100 10 10 100 75 7 1000 350 10 JOHN 24 10 0 800 120 4 15 200 80 SUE 25 3 20 1000 70 1 20 300 95 BETTY 26 1 30 2000 250 10 25 400 110 BOSS 27 3 5000 3000 400 1 30 500 120 6190 9150 1430 What is the ...

charting pivot table results by month
I am trying to create a chart that shows monthly totals from pivot table results. Each record has a date in the first field in the format, 01/01/2004. I have been using the Month function on the date field to change it to a number corresponding to the month before creating the pivot chart. This gives me the monthly totals that I need but I think there must be an easier way. Any suggestions? Thanks. Ken Ken - If you use a custom number format of m or mm, your date will appear as just the month's number, 2 or 02 for February. mmm would give you Feb, and mmmm February. This wouldn'...

Table Names
Does anyone know the table names for Ship To Addresses and for Sales Header data? I need to extract these two tables from SQL and do know what tables to use. Thank you. Regards, Diane All the information you requested can be found in the SOP10100 table. However, if you have individual ship to addresses by line item you will need to look at the SOP10200. You may want to check "Info Flow and Posting" from Accolade Publications (http://www.accoladepublications.com) which is a very simple to understand manual catored to the GP administrator and developer, written by Richard Wh...

Basketball/Any Sport League Table
I know the basics of Excel, but the more different things i try to do, the more complicated the whole thing gets. Anyway, My main aim is to create a league table. so far i have an excel spreadsheet with an entire seasons worth of info in it,aswell as 18 teams worth of individual team performance, so the crowning glory would be a league table. but i am stumped. i have created a new sheet with all of the teams on and used COUNTIF to count win, lose or draw from the relevant sections. The problem now is how to ask excel to differentiate between win and draw in terms of the number of points awa...

Life Insurance table in HR module
Is there any way to increase the decimal places allowed for Insurance Rates? For example our payroll is processed weekly, but the life insurance rate is monthly. So when it is $0.25 / 1000 per month that is $0.577 /1000 per week and the system ONLY allows 2 decimal places. Anyone worked around this? -- Mark VA Yes, allow three decimal places for deductions. -- Charles Allen, MVP "Mark" wrote: > Is there any way to increase the decimal places allowed for Insurance Rates? > For example our payroll is processed weekly, but the life insurance rate is > monthly. So ...