Non-Values & Matrix Linking

[matthewnorton]

Hey all,

I'm not sure whether I should put this in two separate posts, but here goes...

I have just started constructing a database which has many levels.
Some of the field records may not have an entry (as they may not be known) and calculations must be done between these levels. The bottom level is in the form of matrices and I am unsure of what implications this could cause to implementing them in the calculations.

1) I assume from what I know that non-values (or empty record fields), will not work in queries (i.e. they will not be ignored, they will be given a value such as 0 to be used in the calculation). I therefore ask, is it easier to exclude these non-values from the query - or is it better to assign a default value such as -9999, then filter these values out?
Would it be easier to do this exclusion using a filter, or using code?

2) My second question may not even be possible but...
My calculations will involve using values from matrices. I am not sure of the most effective way of using these values in a query? I think there are several options, of which, these two may be the best options.
- turn a matrix from Excel into a table in Access (will mean lots of records - but can be done)
- link a matrix in Excel to an existing query in Access.

Now, the second option may not be possible, but what I would like to do is link a certain cell from Excel to a certain query - depending on the calculation.
i.e. Cell A4 used in Query1 and Cell A5 used in Query2.

I am sorry that I am so ignorant in these matters, but I have been asked to find out what the best way of approaching this is.

Thankyou all very much for your time, I hope what I am trying to achieve is clear!

Kindest regards,

Matt.

 

[FoFa]

1> Empty or NULL values can be delt with in different ways. Yiou can substatute a value for them using the NZ function - NZ([ColumnName],0) would use 0 if the value of ColumnName is NULL as an example. Or you can exlude those rows in a query via the criteria - WHERE [ColumnName] IS NOT NULL as an example. How you deal with NULL values depends on what your requierments mostly.
2> Not 100% sure I understand, but I'll take a stab at it. If the analysis is going to be done using Access, then getting the data in a means that Access can deal with would be the best approach. If it means creating many rows so a query can access it better, that maybe the proper means. It is usually better than creating hundreds of queries to address different columns instead.

 

[matthewnorton]

Fofa,

Thankyou so much for your reply.

For 2), I kinda thought putting it into rows in Access would be the best option - I was just seeing whether anyone knew a more efficient way.

For 1), is that implemented in a validation section in the query - or a filter done previously to the query?

Thankyou once again,

Matt.

 

[FoFa]

1> Depends on you needs. The WHERE portion of course would be in the criteria (selection) or used to filter the data. The NZ function is typically used in the return section I.E. SELECT NZ([Qty],0) as Quantity, [QtyOrd] as Quantity_Ordered, NZ([Qty],0) - [QtyOrd] as Quantity_Left
for example