query for financial year
I would like to write a query where I do not have to 'hard code' a date range for a financial year. The financial year begins 1 July through to 30 June. The column name I am using is [data awarded]. I assume the the code would be part of the WHERE clause in the SQL statement.
|
0 |
|
Utf
1/3/2008 1:18:00 AM
access.queries
6343 articles. 
1 followers.
7 Replies
2439 Views
In query design, type an expression like this into the Field row:
FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
substituting your date field for InvoiceDate.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"pedro" <pedro@discussions.microsoft.com> wrote in message
news:6E33633C-F54A-4493-96A1-1BE46C960F12@microsoft.com...
>I would like to write a query where I do not have to 'hard code' a date
>range
> for a financial year. The financial year begins 1 July through to 30 June.
> The column name I am using is [data awarded]. I assume the the code would
> be
> part of the WHERE clause in the SQL statement.
|
|
0 |
|
Allen
1/3/2008 1:22:07 AM
Sorry, I should have added that I only want the query to return records from
the current financial year.
Thanks for your help.
"Allen Browne" wrote:
> In query design, type an expression like this into the Field row:
> FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
> substituting your date field for InvoiceDate.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "pedro" <pedro@discussions.microsoft.com> wrote in message
> news:6E33633C-F54A-4493-96A1-1BE46C960F12@microsoft.com...
> >I would like to write a query where I do not have to 'hard code' a date
> >range
> > for a financial year. The financial year begins 1 July through to 30 June.
> > The column name I am using is [data awarded]. I assume the the code would
> > be
> > part of the WHERE clause in the SQL statement.
>
>
|
|
0 |
|
Utf
1/3/2008 3:34:00 AM
Pedro -
Here's a sample query based on Northwind's Orders table. It'll prompt you
for the FY start date, e.g. 7/1/94, and will return all records with an Order
date between 7/1/94 and 6/30/95. You should be able to adapt it to your
table and field names:
SELECT
Orders.OrderID
, Orders.CustomerID
, Orders.OrderDate
, Year([fy start date]) AS PolicyYear
FROM
Orders
WHERE
(((Orders.OrderDate) Between [fy start date]
AND
DateAdd("yyyy",1,[fy start date])-1))
ORDER BY
Orders.OrderDate;
HTH - Bob
pedro wrote:
>I would like to write a query where I do not have to 'hard code' a date range
>for a financial year. The financial year begins 1 July through to 30 June.
>The column name I am using is [data awarded]. I assume the the code would be
>part of the WHERE clause in the SQL statement.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1
|
|
0 |
|
raskew
1/3/2008 5:05:00 AM
Current Financial year starts:
DateSerial(Year(DateAdd("m", -6, Date()),7,1)
You can work out the Between expression for your criteria from that.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"pedro" <pedro@discussions.microsoft.com> wrote in message
news:9FE2C667-1DDB-407D-A535-67D920E57220@microsoft.com...
> Sorry, I should have added that I only want the query to return records
> from
> the current financial year.
>
> Thanks for your help.
>
> "Allen Browne" wrote:
>
>> In query design, type an expression like this into the Field row:
>> FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
>> substituting your date field for InvoiceDate.
|
|
0 |
|
Allen
1/3/2008 5:56:58 AM
I get an error message in the between expression:
WHERE [date awarded] between DateSerial(Year(DateAdd("m", -6, Date()),7,1)
and ?
Thanks for your help.
"Allen Browne" wrote:
> Current Financial year starts:
> DateSerial(Year(DateAdd("m", -6, Date()),7,1)
>
> You can work out the Between expression for your criteria from that.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "pedro" <pedro@discussions.microsoft.com> wrote in message
> news:9FE2C667-1DDB-407D-A535-67D920E57220@microsoft.com...
> > Sorry, I should have added that I only want the query to return records
> > from
> > the current financial year.
> >
> > Thanks for your help.
> >
> > "Allen Browne" wrote:
> >
> >> In query design, type an expression like this into the Field row:
> >> FinYearEnd: Year(DateAdd("m", 6, [InvoiceDate]))
> >> substituting your date field for InvoiceDate.
>
>
|
|
0 |
|
Utf
1/4/2008 1:10:00 AM
Okay, I missed a bracket:
DateSerial(Year(DateAdd("m", -6, Date())),7,1)
I'm sure you can work out out to get Year(Date()), and use DateSerial() with
that for 6th month and 30th day.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"pedro" <pedro@discussions.microsoft.com> wrote in message
news:480453F7-D9FA-410F-A035-B6BEED5350B0@microsoft.com...
>I get an error message in the between expression:
>
> WHERE [date awarded] between DateSerial(Year(DateAdd("m", -6, Date()),7,1)
> and ?
>
> Thanks for your help.
>
> "Allen Browne" wrote:
>
>> Current Financial year starts:
>> DateSerial(Year(DateAdd("m", -6, Date()),7,1)
>>
>> You can work out the Between expression for your criteria from that.
|
|
0 |
|
Allen
1/4/2008 1:40:34 AM
You're welcome. Not a problem.
raskew wrote:
>Pedro -
>Here's a sample query based on Northwind's Orders table. It'll prompt you
>for the FY start date, e.g. 7/1/94, and will return all records with an Order
>date between 7/1/94 and 6/30/95. You should be able to adapt it to your
>table and field names:
>
>SELECT
> Orders.OrderID
> , Orders.CustomerID
> , Orders.OrderDate
> , Year([fy start date]) AS PolicyYear
>FROM
> Orders
>WHERE
> (((Orders.OrderDate) Between [fy start date]
>AND
> DateAdd("yyyy",1,[fy start date])-1))
>ORDER BY
> Orders.OrderDate;
>
>HTH - Bob
>>I would like to write a query where I do not have to 'hard code' a date range
>>for a financial year. The financial year begins 1 July through to 30 June.
>>The column name I am using is [data awarded]. I assume the the code would be
>>part of the WHERE clause in the SQL statement.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200801/1
|
|
0 |
|
raskew
1/8/2008 3:21:09 PM
Similar Artilces:
Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...
DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...
Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...
HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...
Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...
assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...
MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...
Fixed Income financial instruments
Good day! I have Money 2006 Standard edition. I invest mainly in fixed income instruments and I am having a lot of trouble with how Money works. Since the FDIC (Federal Deposit Insurance company) gives a customer an insurance for $100,000 per financial institution (banks) I need a report that will add all my cash + investments (CD) by financial institution so make sure that I do not go over the 100,000 limit. So far I have tried and tried but I have not been able to find a way to do it. Money works find with stocks but not so well with fixed income securities. I would like Money to have th...
Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...
Delete Financial Series Inventory Batches
Is there an easier way to delete Financial Series Inventory Batches that we don't want posted to the GL? We don't want to have to open each batch ID and click delete for each of these batches that we need removed. Is there a 'mass delete' functionality that we could use? Thanks. New VBA form can be easily developed to cover this requirment, i don't think you can manage this issue in Great Plains forms. Regards, Mohammad Daoud "Banner" wrote: > Is there an easier way to delete Financial Series Inventory Batches that we > don't want posted to ...
Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...
Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...
sumproduct including previous years
=sumproduct(d18:d1000,--(year(b18:b1000)=year(now()))) I need a formula to add all the values in d18:d1000. All the previous years including current year. Unless you have future dates in Column D along with non-zero values in Column B, wouldn't you just want this? =SUM(D18:D1000) If you do actually have future dates that you have to filter out, try this... =SUMPRODUCT(D18:D1000,--(Year(B18:B1000)<=YEAR(NOW()))) -- Rick (MVP - Excel) "tleehh" <tleehh@discussions.microsoft.com> wrote in message news:2B990606-C1C5-4AE5-8A47-F4F2F598B59B@microso...
Financial Settings in CRM 3.0
Is there any way (supported or un) to change/undo the fiscal period template settings? -- --Dodd ...
Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...
Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...
Bold dates on yearly calendar
I am creating a yearly calendar using a publisher template. How can I bold certain dates on the calendar? When I click on a particular month, all the text for that month gets selected. Thank You About the only way I know is to select the month text box, copy, paste special as a table. The table will be large but can be resized to the same size as the text box by using the size handles. As for the month name cell... select the top row of the table, Table menu, merge cells. You should be able to bold the dates now. -- Mary Sauer http://msauer.mvps.org/ "Ana24" <Ana24@disc...
Year series shows wrong in chart
Hi all : using Excel 2003, I have what should not even be a problem that I cannot solve. I have a series of years in a column, 1970-2006 to be exact, and these are the labels for my x axis. When I insert chart this appears as 1,2,3,...instead of 1970, 1971, etc. It doesn't matter how I format that column, text, general, custom, nothing works. Why is it not just giving me the data in the format that is actually there and how can I get it? thanks for any help Bevin B. Try first entering the dates as dates. To check to make sure they're entered as dates, after entering the data c...
Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...
chart to show employee requests during a given year
I am not very familar with charts or graphs. I would like to create a chart that will track employee requests off during a given year. I need it for excell 2003 any help would be appriciated thanks! ...
Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...
If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...
how do I put in a sequence number on yearly basis?
I need to set a sale price for a product based on the number sold. For example, first 200 will be at 100 each, and 201 to 400 will be at 90 and so on. Is there a way for me to put a new sequence number based on the year? First sold of each year is back at 1? Thank you. You'll need to tell us your table design, i.e. what columns you have. You will need to have a 'date sold' column and a 'quantity' column linked to each sale. You could certainly run a query (or run a DCOUNT function) before you set each price and then set the price depending on the cou...
Running average of yearly ave and monthly data
Hello, I am trying to create a a query that keeps a running percentage of this month's activity versus the yearly average (yealyavg/thismonth). I have already created a query for the yearly average and that is running smooth and updates automatically when new data is added to future months in the table. Goal - to determine % changes in this month's activity versus the yearly average up to this point in time. I am looking at consumer spending habits and want to know where they stand this month (number of purchases) versus their average year to date Table structure, Account Number ...
Ms
I've been struggling with this for some time now and hope that I'm missing something simple. Is it possible to switch the source of a table in MS-Query. The situation. I've created large excel data tapes (150+ fields) which are populated from a SQL Server view via MS-Query. My company has decided to move these views onto a different server. The view I will be using will be the exact same only coming from a different location. Is there an easy way to modify the source of data in MS-Query without rebuilding the entire thing? Please tell me yes. ;) Any help will greatly be ...
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...
DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...
Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...
HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...
Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...
assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...
MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...
Fixed Income financial instruments
Good day! I have Money 2006 Standard edition. I invest mainly in fixed income instruments and I am having a lot of trouble with how Money works. Since the FDIC (Federal Deposit Insurance company) gives a customer an insurance for $100,000 per financial institution (banks) I need a report that will add all my cash + investments (CD) by financial institution so make sure that I do not go over the 100,000 limit. So far I have tried and tried but I have not been able to find a way to do it. Money works find with stocks but not so well with fixed income securities. I would like Money to have th...
Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...
Delete Financial Series Inventory Batches
Is there an easier way to delete Financial Series Inventory Batches that we don't want posted to the GL? We don't want to have to open each batch ID and click delete for each of these batches that we need removed. Is there a 'mass delete' functionality that we could use? Thanks. New VBA form can be easily developed to cover this requirment, i don't think you can manage this issue in Great Plains forms. Regards, Mohammad Daoud "Banner" wrote: > Is there an easier way to delete Financial Series Inventory Batches that we > don't want posted to ...
Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...
Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...
sumproduct including previous years
=sumproduct(d18:d1000,--(year(b18:b1000)=year(now()))) I need a formula to add all the values in d18:d1000. All the previous years including current year. Unless you have future dates in Column D along with non-zero values in Column B, wouldn't you just want this? =SUM(D18:D1000) If you do actually have future dates that you have to filter out, try this... =SUMPRODUCT(D18:D1000,--(Year(B18:B1000)<=YEAR(NOW()))) -- Rick (MVP - Excel) "tleehh" <tleehh@discussions.microsoft.com> wrote in message news:2B990606-C1C5-4AE5-8A47-F4F2F598B59B@microso...
Financial Settings in CRM 3.0
Is there any way (supported or un) to change/undo the fiscal period template settings? -- --Dodd ...
Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...
Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...
Bold dates on yearly calendar
I am creating a yearly calendar using a publisher template. How can I bold certain dates on the calendar? When I click on a particular month, all the text for that month gets selected. Thank You About the only way I know is to select the month text box, copy, paste special as a table. The table will be large but can be resized to the same size as the text box by using the size handles. As for the month name cell... select the top row of the table, Table menu, merge cells. You should be able to bold the dates now. -- Mary Sauer http://msauer.mvps.org/ "Ana24" <Ana24@disc...
Year series shows wrong in chart
Hi all : using Excel 2003, I have what should not even be a problem that I cannot solve. I have a series of years in a column, 1970-2006 to be exact, and these are the labels for my x axis. When I insert chart this appears as 1,2,3,...instead of 1970, 1971, etc. It doesn't matter how I format that column, text, general, custom, nothing works. Why is it not just giving me the data in the format that is actually there and how can I get it? thanks for any help Bevin B. Try first entering the dates as dates. To check to make sure they're entered as dates, after entering the data c...
Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...
chart to show employee requests during a given year
I am not very familar with charts or graphs. I would like to create a chart that will track employee requests off during a given year. I need it for excell 2003 any help would be appriciated thanks! ...
Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...
If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...
how do I put in a sequence number on yearly basis?
I need to set a sale price for a product based on the number sold. For example, first 200 will be at 100 each, and 201 to 400 will be at 90 and so on. Is there a way for me to put a new sequence number based on the year? First sold of each year is back at 1? Thank you. You'll need to tell us your table design, i.e. what columns you have. You will need to have a 'date sold' column and a 'quantity' column linked to each sale. You could certainly run a query (or run a DCOUNT function) before you set each price and then set the price depending on the cou...
Running average of yearly ave and monthly data
Hello, I am trying to create a a query that keeps a running percentage of this month's activity versus the yearly average (yealyavg/thismonth). I have already created a query for the yearly average and that is running smooth and updates automatically when new data is added to future months in the table. Goal - to determine % changes in this month's activity versus the yearly average up to this point in time. I am looking at consumer spending habits and want to know where they stand this month (number of purchases) versus their average year to date Table structure, Account Number ...
Ms
I've been struggling with this for some time now and hope that I'm missing something simple. Is it possible to switch the source of a table in MS-Query. The situation. I've created large excel data tapes (150+ fields) which are populated from a SQL Server view via MS-Query. My company has decided to move these views onto a different server. The view I will be using will be the exact same only coming from a different location. Is there an easy way to modify the source of data in MS-Query without rebuilding the entire thing? Please tell me yes. ;) Any help will greatly be ...