Automate creating query with Nz function

[cheekybuddha]

It also depends on the datatype of the data - it's going to create a mess if they are numeric or date fields

 

[Minty]

@cheekybuddha Yup - The whole premise is a bit leftfield in my experience.
I'm not sure what/why can't be achieved with them being proper Null values.

 

[jaryszek]

Thank you Minty!

do you have maybe any example of changing datafields using VBA in Access?

If i will have "Null" in tables itself i have to replace them before outputting to Nulls once again...

Jacek

 

[jaryszek]

You can join fields. If you have empty column with nulls you can use for example left join to check if you have to remove columns from your database.
So taking raw data form CSV -->Import to Temp Table --> Checking with existing tables what should be added or deleted.Join will not work on null field.

 

[jaryszek]

Ok,

where the my issue is in detais:

INSERT INTO tblTopoTierFuncVolStorageSelector ( TopologyTierFunctionIDFK, VolumeStorageSelectorIDFK )
SELECT qrySource_VolumeShareModes3.TopologyTierFunctionID, qrySource_VolumeShareModes3.VolumeStorageSelectorID
FROM qrySource_VolumeShareModes3 LEFT JOIN tblTopoTierFuncVolStorageSelector ON (qrySource_VolumeShareModes3.VolumeStorageSelectorID = tblTopoTierFuncVolStorageSelector.VolumeStorageSelectorIDFK) AND (qrySource_VolumeShareModes3.TopologyTierFunctionID = tblTopoTierFuncVolStorageSelector.TopologyTierFunctionIDFK)
WHERE (((tblTopoTierFuncVolStorageSelector.VolumeStorageSelectorIDFK) Is Null) AND ((tblTopoTierFuncVolStorageSelector.TopologyTierFunctionIDFK) Is Null));

This is sql generated by Access. And one column is empty unfortunately so i have to add everywhere Nz function to join this...

Jacek

 

[jaryszek]

Anyone please?

Jacek

 

[jaryszek]

Hello,

can anybody help?

Jacek

 

[Minty]

Can you post up a picture of the tables/relationships?
I'm struggling to see the wood for the trees with the raw SQL

 

[jaryszek]

Hi Minty,

thanks!
Of course, the query from which i am struggling is:

SELECT qrySource_VolumeMountOptions4.OSType, qrySource_VolumeMountOptions4.OSDistro, qrySource_VolumeMountOptions4.OSVersion, qrySource_VolumeMountOptions4.StorageProtocal, qrySource_VolumeMountOptions4.ProtocolVersion, qrySource_VolumeMountOptions4.ProviderFileSystemType, qrySource_VolumeMountOptions4.ConsumerFileSystemType, qrySource_VolumeMountOptions4.ConsumerFulfillment, qrySource_VolumeMountOptions4.ProviderFulfillment, qrySource_VolumeMountOptions4.ProviderMountOptions, qrySource_VolumeMountOptions4.ConsumerMountOptions, qrySource_VolumeMountOptions4.ProviderLinuxMountFreq, qrySource_VolumeMountOptions4.ConsumerLinuxMountFreq, qrySource_VolumeMountOptions4.ProviderLinuxMountPassno, qrySource_VolumeMountOptions4.TopoTierFuncVolStorageSelectorID
FROM qrySource_VolumeMountOptions4 LEFT JOIN tblVolumeOSMountOptions ON (qrySource_VolumeMountOptions4.ProviderLinuxMountPassno = tblVolumeOSMountOptions.ProviderLinuxMountPassno) AND (qrySource_VolumeMountOptions4.ConsumerLinuxMountFreq = tblVolumeOSMountOptions.ConsumerLinuxMountFreq) AND (qrySource_VolumeMountOptions4.ProviderLinuxMountFreq = tblVolumeOSMountOptions.ProviderLinuxMountFreq) AND (qrySource_VolumeMountOptions4.ConsumerMountOptions = tblVolumeOSMountOptions.ConsumerMountOptions) AND (qrySource_VolumeMountOptions4.ProviderMountOptions = tblVolumeOSMountOptions.ProviderMountOptions) AND (qrySource_VolumeMountOptions4.ProviderFulfillment = tblVolumeOSMountOptions.ProviderFulfillment) AND (qrySource_VolumeMountOptions4.ConsumerFulfillment = tblVolumeOSMountOptions.ConsumerFulfillment) AND (qrySource_VolumeMountOptions4.ConsumerFileSystemType = tblVolumeOSMountOptions.ConsumerFileSystemType) AND (qrySource_VolumeMountOptions4.ProviderFileSystemType = tblVolumeOSMountOptions.ProviderFileSystemType) AND (qrySource_VolumeMountOptions4.ProtocolVersion = tblVolumeOSMountOptions.ProtocolVersion) AND (qrySource_VolumeMountOptions4.StorageProtocal = tblVolumeOSMountOptions.StorageProtocol) AND (qrySource_VolumeMountOptions4.OSVersion = tblVolumeOSMountOptions.OSVersion) AND (qrySource_VolumeMountOptions4.OSDistro = tblVolumeOSMountOptions.OSDistro) AND (qrySource_VolumeMountOptions4.OSType = tblVolumeOSMountOptions.OSType) AND (qrySource_VolumeMountOptions4.TopoTierFuncVolStorageSelectorID = tblVolumeOSMountOptions.TopoTierFuncVolStorageSelectorIDFK)
WHERE (((tblVolumeOSMountOptions.TopoTierFuncVolStorageSelectorIDFK) Is Null) AND ((tblVolumeOSMountOptions.OSType) Is Null) AND ((tblVolumeOSMountOptions.OSDistro) Is Null) AND ((tblVolumeOSMountOptions.OSVersion) Is Null) AND ((tblVolumeOSMountOptions.StorageProtocol) Is Null) AND ((tblVolumeOSMountOptions.ProtocolVersion) Is Null) AND ((tblVolumeOSMountOptions.ProviderFileSystemType) Is Null) AND ((tblVolumeOSMountOptions.ConsumerFileSystemType) Is Null) AND ((tblVolumeOSMountOptions.ConsumerFulfillment) Is Null) AND ((tblVolumeOSMountOptions.ProviderFulfillment) Is Null) AND ((tblVolumeOSMountOptions.ProviderMountOptions) Is Null) AND ((tblVolumeOSMountOptions.ConsumerMountOptions) Is Null) AND ((tblVolumeOSMountOptions.ProviderLinuxMountFreq) Is Null) AND ((tblVolumeOSMountOptions.ConsumerLinuxMountFreq) Is Null) AND ((tblVolumeOSMountOptions.ProviderLinuxMountPassno) Is Null));

so you can see how many Nz Functions should i add here to make this working.
SourceQuery4 here is a CSV file.

TblVolumeOsMountOptions is target table.

So now i have to insert what not exists in sourcetable in targettable.

Best,
Jacek

 

[cheekybuddha]

Hi,

I know you wanted me to just follow this thread, but I think it's not necessary to use Nz() here. (it's quite likely I've misunderstood) but really you just want to select the records in qrySource_VolumeMountOptions that are not already present in their entirety in tblVolumeOSMountOptions (ie matched on all the fields).

For that you should be able to use:

SELECT
  q.OSType,
  q.OSDistro,
  q.OSVersion,
  q.StorageProtocal,
  q.ProtocolVersion,
  q.ProviderFileSystemType,
  q.ConsumerFileSystemType,
  q.ConsumerFulfillment,
  q.ProviderFulfillment,
  q.ProviderMountOptions,
  q.ConsumerMountOptions,
  q.ProviderLinuxMountFreq,
  q.ConsumerLinuxMountFreq,
  q.ProviderLinuxMountPassno,
  q.TopoTierFuncVolStorageSelectorID
FROM qrySource_VolumeMountOptions4 q
LEFT JOIN tblVolumeOSMountOptions t
       ON q.ProviderLinuxMountPassno = t.ProviderLinuxMountPassno
      AND q.ConsumerLinuxMountFreq = t.ConsumerLinuxMountFreq
      AND q.ProviderLinuxMountFreq = t.ProviderLinuxMountFreq 
      AND q.ConsumerMountOptions = t.ConsumerMountOptions
      AND q.ProviderMountOptions = t.ProviderMountOptions
      AND q.ProviderFulfillment = t.ProviderFulfillment
      AND q.ConsumerFulfillment = t.ConsumerFulfillment
      AND q.ConsumerFileSystemType = t.ConsumerFileSystemType
      AND q.ProviderFileSystemType = t.ProviderFileSystemType
      AND q.ProtocolVersion = t.ProtocolVersion
      AND q.StorageProtocal = t.StorageProtocol
      AND q.OSVersion = t.OSVersion
      AND q.OSDistro = t.OSDistro
      AND q.OSType = t.OSType
      AND q.TopoTierFuncVolStorageSelectorID = t.TopoTierFuncVolStorageSelectorIDFK
WHERE t.VolumeMountOSOptionID Is Null
;

If you are trying to fill in the gaps in certain fields in tblVolumeOSMountOptions on partially-matched records then it becomes more tricky.

If you are saying that you are trying to match fields which could be zero-length strings in the table and NULL in the query, then you will have to use Nz() on each match condition, or just perform an Update first to set all ZLS's to NULL (or vice versa) first.

hth,

d

 

[jaryszek]

hi cheekybuddha,

thank you!

then you will have to use Nz() on each match condition

This is exactly what i am trying to automate here - this is why i created the topic.

 

[cheekybuddha]

Can you post a db with just the table an query involved?

Is it the query that has ZLS's or the table? Or both?

 

[jaryszek]

Hi,

thanks.
I can not share my databse unfortunately but i will prepare example database.
With temp CSV with empty table and database table where i have to check what exists or not using joins on empty fields also.

Best,
Jacek

 

[jaryszek]

Ok Guys,

i solved the problem. I have just build an Excel which is converting all field names for Nz statements.

 

[cheekybuddha]