qry for Excel report with weekly data in a vertical column

tblArea 
AreaID 
area 



tblHeadEnd 
HeadEndID 
AreaID 
headend 

tblRouter 
RouterID 
HeadEndID 
router 

tblPorts 
PortID 
RouterID 

tblTransmitter 
TransmitterID 
PortID 
transmitter 
PortID 
RouterID 

tblNodes 
NodeID 
transmitterID 


tblNodeNamesID 
NodeID 
ParentNodeName 
date created 

tblUtilization 
utilID 
NodeID 
percentage 
Date 

The report shows weekly utilization data  for an ISP.  This structure is 1:M 
in the areas to the headend.  The headend has 1:m routers, each router has 
1:M ports.  The transmitter can have either 1:1 to nodes or 1:M .  The 
nodeNames table is for the purpose of being able to split a node into 2 
nodes.  I don't need to get into that here but I need a script to allow the 
user to do it.

 I need to get the weekly percent of utilization >50%  in Excel format in a 
vertical column.  Each week I want to add a new column for each weekending.  
I wonder how to do that with SQL?  I plan on making an Excel template to 
import the data.

There is one anomaly for the SQL query.  The data only makes sense to the 
user from the perspective of the router port.  All  of the nodes related to 
one port need to be on the same row in Excel how do I do this?  In other 
words each row would be a node but that would make the report non-sensical to 
the users.

tia,
0
Utf
8/6/2007 2:26:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1017 Views

Similar Articles

[PageSpeed] 39

Since the utilization data is a column perhaps this is a crosstab query??  Is 
a crosstab query just when the rows are reversed into columns?  If so then I 
may have answered my own question.

"Janis" wrote:

> tblArea 
> AreaID 
> area 
> 
> 
> 
> tblHeadEnd 
> HeadEndID 
> AreaID 
> headend 
> 
> tblRouter 
> RouterID 
> HeadEndID 
> router 
> 
> tblPorts 
> PortID 
> RouterID 
> 
> tblTransmitter 
> TransmitterID 
> PortID 
> transmitter 
> PortID 
> RouterID 
> 
> tblNodes 
> NodeID 
> transmitterID 
> 
> 
> tblNodeNamesID 
> NodeID 
> ParentNodeName 
> date created 
> 
> tblUtilization 
> utilID 
> NodeID 
> percentage 
> Date 
> 
> The report shows weekly utilization data  for an ISP.  This structure is 1:M 
> in the areas to the headend.  The headend has 1:m routers, each router has 
> 1:M ports.  The transmitter can have either 1:1 to nodes or 1:M .  The 
> nodeNames table is for the purpose of being able to split a node into 2 
> nodes.  I don't need to get into that here but I need a script to allow the 
> user to do it.
> 
>  I need to get the weekly percent of utilization >50%  in Excel format in a 
> vertical column.  Each week I want to add a new column for each weekending.  
> I wonder how to do that with SQL?  I plan on making an Excel template to 
> import the data.
> 
> There is one anomaly for the SQL query.  The data only makes sense to the 
> user from the perspective of the router port.  All  of the nodes related to 
> one port need to be on the same row in Excel how do I do this?  In other 
> words each row would be a node but that would make the report non-sensical to 
> the users.
> 
> tia,
0
Utf
8/6/2007 4:56:00 PM
Reply:

Similar Artilces:

Report chart order
I have 5 picklist attributes that I want to display along the x axis of a chart in my report. I can't sort by obvious methods (i.e. by data value of the picklist or by alphabetical order of the names) as these still produce data that is in the wrong order for my purposes. I know I could rename everything in the picklist to have a prefix 1, 2, 3 etc, but I'm not really too keen on this. Is it possible to explicitly define where I want each value on the x axis? ...

Excel question #10
How can I set up a formula to h'light a complete row, if one entry meets a set criteria in excel? Not sure if above is possible, but basically I need to use a highlight funtion as a quick filter, so anyone netering spreadsheet can imediatley see the data they are looking for. I will be using letters instead of numbers also, so if the field is blank it will highlight complete row, if cell is populated, cell will unhighlight hi use 'Format -Conditional Format' for this. See: http://cpearson.com/excel/cformatting.htm >-----Original Message----- >How can I set up a formula ...

Create Excel file with VBA in Access
Here are 2 examples that I have found to create an Excel file with VBA. It is great for exporting data to Excel with the format that you want. Michael '---------------------------------------------------------------- Sub Test_Excel() Dim MyXL As Object 'Excel Application Object Dim XL_File As String Dim SheetName As String 'Dim xlBook As Excel.Workbook XL_File = "C:\Excel_Documents\test.xls" SheetName = "New Sheet Name" 'Create the Excel Application Object. Set MyXL = CreateObject("Excel.Application") 'Create new Excel Workbook MyXL....

datas ms access to excel
Hallo, I have a certain amount of datas, which I put into MS Access. A part o those datas should now AUTOMATICALLY forwarded (duplicated) to excel. IE: In Access there is a row called item, the next is item number. Whe I put those subjects into Access -the same rows are in Excel- shoul duplicate it to Excel (item, item number). What is the most convenient way to do this? BTW I use MS Office 97. Cheers Juerge -- Message posted from http://www.ExcelForum.com You have 2 basic alternatives :- In Access - File/Save As .. Export. In Excel - Data/Get External Dat -- Message posted from http...

excel 2000 and excel 2003
All my spreadsheets that have been made in excel 2000 don't print out in excel 2003 as they do in excel 2000. The margins are off and the page breaks blend into the next page. I got a new PC and it has office 2003 and all my excel files from my old PC have this problem. Is this a known issue when moving from 2000 to 2003? Are your OS display resolutions on the new PC the same as old PC? Is the printer and driver on the new PC the same as old PC? If not, you will probably have problems, but not any fault of Excel's. Gord Dibben MS Excel MVP On Wed, 3 Jan 2007 18:07:01 -0800,...

Need help with EXCEL?
Try this site: http://TheGoodStuff.cc ...

How to use data on two forms
I have two forms. A button on form1, and when i click on button1 my form1, form2 is open. Add some data to form2, and write it on form1, all with class. How to do that? imarkic <imarkic84@gmail.com> wrote in news:009b8c57-bc47-409a-8f3a- e995e1944f19@j5g2000yqm.googlegroups.com: > I have two forms. A button on form1, and when i click on button1 my > form1, form2 is open. Add some data to form2, and write it on form1, > all with class. > How to do that? > This really depends on the model you are using. In general, you will not control it from an outside c...

how do I tabulate data in excel 2003
I need to tabulate some data from 500 surveys with 30 questions. How do I do that? On May 13, 11:36=A0am, Becky <Be...@discussions.microsoft.com> wrote: > I need to tabulate some data from 500 surveys with 30 questions. How do I= do > that? How is your data organized? You start by pretending we cannot see your worksheet and its layout. Then you provide some details. Gord Dibben MS Excel MVP On Thu, 13 May 2010 11:36:01 -0700, Becky <Becky@discussions.microsoft.com> wrote: >I need to tabulate some data from 500 surveys with 30 questions. How do...

Opening .dat files created by Excel for Mac
I createsd an Excel workbook on a Mac and emailed it to my self. The atchment is .dat (but that may have just been added by my email program as macs don't have file extensions, Ithink). I cannot open it in Excel XP running on Windows XP. I tried renaming it .xls and various other excel extensions but it still tells me that it dosen't recognize the file. If I open it in a text editor, I can clearly see many of the custom number formats, names, lables, and some cell values I used in the file, as well as formating for a graph I encluded, the name of the worksheets and meaningles...

Excel 97 and Excel 2002 #2
I upgraded to Office XP. I have an expensive piece of software (an Excel Add In) that won;t work with Excel 2002 but does work with Excel 97. Can I load Excel 97 onto a machine that already has Excel 2002 so I can keep using the software? How can I do that so I don't mess everthing up? Thanks. Install 97 in a distinct separate folder. Say No if you're prompted to replace or overwrite anything. Then reinstall 2002, say No to all replace/overwrite questions. But why won't the addin work in later versions ? It should. HTH. Best wishes Harald "sslezak" <sslezak@...

PM edit list exception report
Is there a way to print a PM Edit list showing only transactions with errors that will not post? Or a full list with the error transactions at the top or something like this? -- JayStar ...

exit excel
Does anyone know the vb code which will enable me to attach to a button which will exit out of excel to the desk top? Many thanks Application.Quit You may want to precede this with Application.DisplayAlerts = False Bob Umlas Excel MVP "welshwizzard" <welshwizzard@discussions.microsoft.com> wrote in message news:B26198B4-DB5A-46FA-AD17-2A3521BF2A90@microsoft.com... > Does anyone know the vb code which will enable me to attach to a button > which > will exit out of excel to the desk top? > Many thanks ...

Export to Excel problem
Hello all I have tried to export a list to Excel. I click on the Excel button, and I am asked whether I want to open or save the file. If I cancel, CRM closes. If I click open, Excel opens, gets populated and then CRM closes. If I click save i get prompted to save the list, Excel opens with the data and CRM closes. What gives? how can i create a dynamic worksheet? Why is CRM closing? TIA Saira this sort of behavior is normally caused by popup blockers in IE. However I have not heard of this sort of behaviour in Excel. have you seen similar issues just using CRM or is it only when exp...

Excel Charts from pivot tables without scroll down bar?
Is there an easy way to copy/paste a pivot table chart into powerpoint without scroll down bars? If you hide them in Excel: On the PivotTable toolbar, choose PivotChart>Hide PivotChart Field Buttons they should also be hidden when you paste the chart into PowerPoint. Angel wrote: > Is there an easy way to copy/paste a pivot table chart into powerpoint > without scroll down bars? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html ...

Excel not opening
Hi, When I double click Excell icon from the desktop, it opens excell without the default new sheet. when I double click any excell file from explorer, then excell and the file opens normally. Why excell is behaving like this? Pls help. Thanks. -- Athar Sagri Hi, Look at the icon properties and remove the switches such as "/e" or "/embedded" after "Excel.exe". HTH Daniel > Hi, > > When I double click Excell icon from the desktop, it opens excell without > the default new sheet. when I double click any excell file from explorer, > then exce...

how to rotate excel table in word
I insert a excel worksheet in word and like to rotate it Select the area in the worksheet you want to move to Word. While hold down the shift key, pull-down Edit > Copy Picture. In Word, just paste. Because the item is now a picture, you can use the drawing toolbar to rotate it to any desired angle -- Gary''s Student "dro" wrote: > I insert a excel worksheet in word and like to rotate it Thanks, its working just fine for me. "Gary''s Student" wrote: > Select the area in the worksheet you want to move to Word. While hold down > the ...

qry for dates in 1st 2nd 3rd and forth quarters of current year!
Hello i am looking to write a query which returns values for the current year only! DatePart("q",[Start Date],Year([start date])=Year(Date())) is what i have written but this includes enteries for next year which i dont need! any ideas please!!! thanks in advance hi, smason wrote: > DatePart("q",[Start Date],Year([start date])=Year(Date())) > is what i have written but this includes enteries for next year which i > dont need! any ideas please!!! You need to filter for the current year, e.g. SELECT DatePart("q",[Start Date]) FROM [yourT...

Is there a Bowling score card template available in Excel?
I am looking for a ready-to-use bowling score card template in Excel. Thanks, M.B.Tobin ...

Excel Problems #7
I have recently converted from Windows 98 to Windows XP. My files partially migrated. I have tried reinstalling MS Office but Excel and Word are only partially installed. I have tried copying documents but XP can not open them. I have tried everything I can think of. Please help. What am I doing wrong? -- Thanks Tammy Try Opening WORD or EXCEL and then importing the documents. On My XP machine if I try doing something - and I get the error message like, "that option is not installed" and "would you like to install it" I just select "YES" and it installs tha...

money excel
I need to export an account from Money to Excel. I.m using the export command but I got the data in EXCEL all in a single column and no in different columns as I want. Can someone give me a hand with this please. Thanks ALEX See if http://umpmfaq.info/faqdb.php?q=9 helps. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback "alex" <anonymous@...

Open XL
If you want to do any or all of the following. Visit http://www.excelitems.com and Download Open XL. 1. Run Charts Slideshow 2. Calculate Business Working Days 3. Sort Worksheets 4. Merge Worksheets 5. Superhide a worksheet 6. Copy Multiple Selections 7. Learn VBA/VSTO 8. Copy Worksheets to MS Word 9. Invert the Selection 10. Remove all Empty Rows 11. Delete Blank Sheets and run such 125 utilities from single addin. Open XL is a freeware addin. Download, Install and Use. This is definitely gonna save your lots of hours. Enjoy Excel ! Regards Ashish Jain www.excelitems.com Developer of ...

Footers in Excel
How do I put footers and dates on all Excel worksheets in just one go? Have several XLS files that have 10's of worksheets. Right Click>select all sheets>view>header and footers>footer>(add your footer info> click in any cell to deselect grouped sheets>print preview to view results Pat F "Hans" wrote: > How do I put footers and dates on all Excel worksheets in just one go? > Have several XLS files that have 10's of worksheets. > > > Right-click a worksheet tab and choose "Select All Sheets", then set your footer. Righ...

Faxing from Excel?!
Hello, I have a guest membership application that I've created. It prints invitations and then employees can mail them to potential guests that have asked for an invitation. I've added the ability to create an invitation and then email it as an attachment. Now, the folks want to be able to send the invitation to fax numbers. Does anyone know any code to fax from Excel VBA? I am using Excel 2007. Most of the pcs are Windows XP with faxing services loaded. But I need this to be part of the current application. So I would like to call whatever from Excel VBA...

Install FrontPage for excel?
Hi. Lucky me...first post on this forum but thanks in advance if you can help. I recently performed an update. Since today I notice all programs seem to open without problem except for excel. It wants me to insall excel with frontpage before opening. Naturally it does not open and I don't have a CD with frontpage. What shall I do? How can I redirect all existing documents to excel without frontpage and also ensure future docs. don't need it? -- thanks....jkstar jkstar - > ... It wants me to insall excel with frontpage before opening. ... < First, what is the &...

Shortcuts in Explorer not working for Excel, but opens within Excel
Shortcuts in Explorer not working for Excel, but opens within Excel (periodically) Thought it might be memory...still may be the issue, but not sure. See one reply at your previous post. "Brian @ SMS" wrote: > > Shortcuts in Explorer not working for Excel, but opens > within Excel (periodically) > > Thought it might be memory...still may be the issue, but > not sure. -- Dave Peterson ec35720@msn.com ...