MS Query

[MarkAndrews]

I have a problem with MS Query

It isn't copying some data over (which derive from an excel formula & is numbers)

Any idea's?

TIA

 

[lozzablake]

What do you mean by not copying. Do you mean it is not displaying all the records that you expect to receive back?

 

[MarkAndrews]

I have a sheet full of data, I use MS Query to extract the data i need (via a validation cell)

I then use this query & combine various countif's etc for my data colation & charts

Abort - Jan.xls
	A	B	C	D	E	F	G	H	I	J
1	ClientID	IntroducerID	MatterID	StartDate	AbortDate	AuthActDate	AuthactReceived	Client	Networkdays	Introducer
2	10	40	563785	03/01/06	NULL	NULL	No	GenericSAP		LegalMove
3	10	40	563787	03/01/06	NULL	NULL	No	GenericSAP		LegalMove
4	21	33	563790	03/01/06	NULL	08/02/06	Yes	FirstActive(REM)		FirstActive
5	21	33	563791	03/01/06	10/01/06	NULL	No	FirstActive(REM)	6	FirstActive
Jan

Column E - Dates are not being displayed in the SQL Query, nor the networkdays

(Above example is of the raw data before running it through the query)

I have redone the query to no avail

I am stuck!

Thanks

 

[lozzablake]

If you right click on the query and choose edit query and then go through the options until you get to disply in Excel or edit query, choose edit query.

This displays the SQL query window. Is the data still showing as NULL in that. If so then your underlying data has Nulls and you need to fix that.

 

[MarkAndrews]

This is my generated SQL

SELECT `Data$`.ClientID, `Data$`.IntroducerID, `Data$`.MatterID, `Data$`.StartDate, `Data$`.AbortDate, `Data$`.AuthActDate, `Data$`.`Authact Received`, `Data$`.Client, `Data$`.Networkdays, `Data$`.Introducer
FROM `P:\MI & Analysis\Abort - Jan`.`Data$` `Data$`
WHERE (`Data$`.Introducer Like ?)

 

[lozzablake]

You need to disitnguish between whether it is a problem in Excel or a problem in the underlying data. Is AuthAcDate allowed to have nulls in the underlying data? If so then Excel is just displaying what is there.

 

[MarkAndrews]

You need to disitnguish between whether it is a problem in Excel or a problem in the underlying data. Is AuthAcDate allowed to have nulls in the underlying data? If so then Excel is just displaying what is there.

AuthAcDate has Null & dates in the raw data - see below data

I need it to show both NULL & a date (if there is one in the column) in the query

Abort - Jan.xls
	F	G	H	I
1	AuthActDate	AuthactReceived	Client	Networkdays
2	NULL	No	GenericSAP
3	NULL	No	GenericSAP
4	08/02/06	Yes	FirstActive(REM)
5	NULL	No	FirstActive(REM)	6
6	NULL	No	GenericSAP
7	NULL	No	GenericSAP
8	NULL	No	GenericSAP
9	NULL	No	GenericSAP
10	NULL	No	Halifax
11	NULL	No	GenericSAP	163
12	NULL	No	OffsetNatWest(PAD)	13
13	NULL	No	GenericSAP
14	NULL	No	GenericSAP
15	10/01/06	Yes	GenericSAP	3
16	NULL	No	WOL	78
17	NULL	No	WOL	6
18	25/01/06	Yes	WOL	133
19	NULL	No	WOL	56
20	31/01/06	Yes	WOL	93
21	NULL	No	WOL	32
22	NULL	No	WOL	133
23	NULL	No	Halifax	4
24	NULL	No	WoolINT	2
25	NULL	No	GenericSAP	3
26	06/01/06	Yes	BBS	152
27	NULL	No	GenericSAP	73
28	NULL	No	OffsetRBS(REM)	77
Data

 

[lozzablake]

Sorry, I do not understand the problem. If there is a null in the date field then there is clearly going to be a null in networkdays.

The formulae are showing the right results. Do you want them to show something else?

 

[MarkAndrews]

The Null, Is the word Null

It's not copying (if and when) there are dates in that column, which is what i need

The networkdays works from this column and the start date - these are not also displaying in the MS Query (query)

I need both the Null & Date (if there is one) displaying in the query, to get the networkdays result for my chart..?

 

[MarkAndrews]

I could email you the file - would this make more sense?