Please help - New user of SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Please help - New user of SQL

Author

Topic

Therese
Starting Member

12 Posts

Posted - 2007-09-15 : 20:28:14

Hi,

It's my first time in the forum and hope some of the gurus can help resolving my question.

I have a table of billing data where I would like to identify the first billing data exist for each record.

Table Name : Billing
Fields : Jan-06, Feb-06, Mar-06 for the whole Year of 2006 and the same fields also being used for 2007.

Each record in the Billing table has different Billing date. All I want to do is to write a simple SQL statement to find out the First Billing Month for each record.

Please note that I'm using MS Access SQL.

Many thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-15 : 20:33:28

Moved from "New to SQL Server" since it's an Access question.

so you have a column for each month?
ever heard of normalization?
you have a billing db in access??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Therese
Starting Member

12 Posts

Posted - 2007-09-15 : 20:40:23

Thanks spirit1,

It's only a small Billing db in Access (about 500 records)
And I have never heard of normalization.
Is there a way to solve my question?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-15 : 20:45:14

can you show sample data and expected result based on that sample data?

normalization:
http://www.datamodel.org/NormalizationRules.html

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Therese
Starting Member

12 Posts

Posted - 2007-09-15 : 21:03:54

Thanks again!

Sample data:

Account Jan06 Feb06 Mar06 Apr06 May06 Jun06 First Billing Month

Mr ABC 25.1 30.0 15.6 10.8 19.25
Mr XY 89.05 90.25 68.95 89.90 101.2 99.50
Ms 123 20.65 30.8 31.2

I would like to see the result in 'First Billing Month' field to appear
Mr ABC = Feb-06
Mr XY = Jan-06
Ms 123 = Apr-06
after running a query.

Is it achievable?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-16 : 06:03:14

SELECT Account, IIF(ISNULL(Jan06), IIF(ISNULL(Feb06), IIF(ISNULL(Mar06), IIF(ISNULL(Apr06), IIF(ISNULL(May06), IIF(ISNULL(Jun06), IIF(ISNULL(Jul06), IIF(ISNULL(Aug06), IIF(ISNULL(Sep06), IIF(ISNULL(Oct06), IIF(ISNULL(Nov06), IIF(ISNULL(Dec06), 'None', 'Dec06'), 'Nov06'), Oct06'), 'Sep06'), 'Aug06'), 'Jul06'), 'Jun06'), 'May06'), 'Apr06'), 'Mar06'), 'Feb06'), 'Jan06')
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

Therese
Starting Member

12 Posts

Posted - 2007-09-16 : 06:11:48

Thanks very much Peso. I'll try and let you know the result.

Therese
Starting Member

12 Posts

Posted - 2007-09-16 : 19:59:39

What if I would like to add another condition that the return field (where the result to be shown) is Null as well.

I tried the below but didn't work:

IIf("IsNull[First Tracked]" And ([Jan06]>0),#01/01/2006#,""), IIf("IsNull[First Tracked]" And ([Feb06]>0),#01/02/2006#,""), IIf("IsNull[First Tracked]" And ([Mar06]>0),#01/03/2006#,"")

The return field here is [First Tracked]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 00:58:19

Drop the double quotes around "ISNULL([First tracked])" and add paranthesis instead.

E 12°55'05.25"
N 56°04'39.16"

Therese
Starting Member

12 Posts

Posted - 2007-09-17 : 01:45:21

Thanks heaps Peso. I tried the below but still didn't work. Once I input the statement in Update To panel, it's automatically input the double quotes (")

IIf(IsNull[First Tracked] And ([Jan06]>0),#01/01/2006#,""), IIf(IsNull[First Tracked] And ([Feb06]>0),#01/02/2006#,""), IIf(IsNull[First Tracked] And ([Mar06]>0),#01/03/2006#,"")

Plus the separator of comma (,) doesn't seem to be correct. I replaced with AND/OR but the result is incorrect.

Sorry for my ignorance. I'm only new to all this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 02:34:11

1) You missed the paranthesises!
2) You also miss the IIF third argument.
3) Is [First Tracked] a column/field in the table?
4) Or are you trying to update the [First tracked] column/field to a new value?

IIf(IsNull([First Tracked]) And Not ISNULL([Jan06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Feb06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Mar06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Apr06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([May06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Jun06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Jul06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Aug06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Sep06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Oct06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Nov06]), #01/01/2006#, IsNull([First Tracked]) And Not ISNULL([Dec06]), #01/01/2006#, "None")))))))))))))

E 12°55'05.25"
N 56°04'39.16"

Therese
Starting Member

12 Posts

Posted - 2007-09-17 : 05:40:13

Thanks Peso. I still received the error message of "contain wrong number of arguments". Think I should make it simpler by using the below sample data:

I have the below 7 columns - 1st row is column name and the remaining 3 rows are data.

Query1
Account Jan06 Feb06 Mar06 Apr06 May06 First Tracked
Mr ABC 356.25 452.1
Mr XYZ 256.12 50.5 100.9 200 650
Ms Daisy 40.9 50.8 60.7 50.5
Mrs Jones 90.2 82.5 65 01/03/2006

First Tracked is a Date field where I would like the return of query to appear.
Last record of Mrs Jones already has a result of 01/03/2006 for being the first value billed in Mar06 field.

All I want to do is to update the same result of other records in First Tracked field.

THanks!

Therese
Starting Member

12 Posts

Posted - 2007-09-17 : 05:42:39

Sorry, since the table cannot be pasted probably, I should add that
Mr ABC's first value appears in Apr06
Mr XYZ's first value appears in Jan06
Ms Daisy's first value appears in Feb06
And of course, Mrs Jones's first value appears in Mar06

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 06:07:07

[code]SELECT Account,
IIf(IsNull([First Tracked]) And Not ISNULL([Jan06]), #01/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Feb06]), #02/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Mar06]), #03/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Apr06]), #04/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([May06]), #05/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Jun06]), #06/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Jul06]), #07/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Aug06]), #08/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Sep06]), #09/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Oct06]), #10/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Nov06]), #11/01/2006#,
IIf(IsNull([First Tracked]) And Not ISNULL([Dec06]), #12/01/2006#,
0))))))))))))
FROM Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 06:17:00

Use the [ c o d e ] and [ / c o d e ] tags (without spaces) to post table data correctly.

E 12°55'05.25"
N 56°04'39.16"

Therese
Starting Member

12 Posts

Posted - 2007-09-17 : 06:23:26

Tested on a few sample data and it works.
Will try the full database and let you know the results.

Thanks!

Therese
Starting Member

12 Posts

Posted - 2007-09-17 : 23:31:07

Thanks so much Peso, it works! :)

One more question.

Now that I have the "First Tracked" field filled with correct data.
I also have another field called "Last Tracked" where the data to be calculated up to 13 months from the date in "First Tracked" field. What function should I use to update it correctly for "Last Tracked" field?

For example:

First Tracked = 02/01/2006 & 04/01/2007

I would like to update so that the "Last Tracked" field will return with

Last Tracked = 02/28/2007 & 04/30/2008

I tried to add the "First Tracked" field with 395 days (ie. 365 days + 30 days) but it didn't work!

Thanks heaps.

Therese
Starting Member

12 Posts

Posted - 2007-09-18 : 00:32:25

I found the answer for this. Used the below function to update in the "Last Tracked" field and it worked.

DateSerial(Year([First Tracked]),Month([First Tracked])+13,Day([First Tracked]))

However, I have a different question do not know if it's achievable.

I have two duplicate records for 1 account where it has two different sets of start and end dates. But it has an overlapping period and that's what I would like to find out and put a ratio in it.

Here is the sample:

Account: Mr XYZ has two billing records as per below

1st record with a value in "First Tracked" field of 06/01/2006 and the value of "Last Tracked" field of 07/01/2007 (13 months later)
2nd record with a value in "First Tracked" field of 04/01/2007 and the value of "Last Tracked" field of 05/01/2008 (13 months later)

The overlapping period between two records is from 04/01/2007 to 07/01/2007

I put a ratio for each month of billing is 100% for those months that not overlapped. But for the overlapping month I need to put in a ratio so that it's also equal 100% for each account.

Month - Jun06-Jul06-Aug06-Sep06-Oct06-Nov06-Dec06-Jan07-Feb07-Mar07-Apr07-May07-Jun07-Jul07-Aug07-Sep07-Oct07-Nov07-Dec07-Jan08
1stRec- 100%-100%-100%-100%-100%-100%-100%-100%-100%-50%-50%-50%--------------------------------------
2ndRec-----------------------------------------------50%-50%-50%-100%-100%-100%-100%-100%-100%-100%-100%

Does this make sense?

Therese
Starting Member

12 Posts

Posted - 2007-09-18 : 17:48:58

Any luck in resolving this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources