Using an updatable constant in a query

[scoggy]

Hello all. I've been reading this forum trying to get some info, and have picked up some great tips - thanks!

I'm a bit of a novice with Access (using Acess 2003), as I think my question will demonstrate. I've searched for help on this but part of my problem is that I don't know what search terms to use! I'm reasonably good with VBA in Excel, and would eventually like to get more sophisticated with Access.

I have a query on my Finance database, in which I'd like to have a calculation based on a month end date. I want to be able to update that date each month. I want to update the month end date manually rather than calculate it.

Currently I'm trying to do this by putting the date in a single field table as the sole record. I can see this isn't working (since clearly I can't add that table into a query without linking it to one of the other tables) so suspect I'm on the wrong track.

Can anyone help me please?

 

[r.harrison]

Hi,

Just a quick question. Do you need to store the month end figures in a table or just run a query each month to get the total?

 

[Galaxiom]

By definition a "constant" is not updateable.

A single record in a table can be used in a query. It does not need a join. Just add the table to the query and refer to the field.

However one would normally enter a value like that into control on a form and refer to the control in a query. The control would have a default date, usually the current or previous month.

 

[scoggy]

Thanks for the responses.

@r.harrison: My idea was to update the month end date when I come to run the processes each month. Whether this is stored in a table or elsewhere I don't really mind. I just need my query to have access to it - eg to work out how old a debt is.

@GalaxiomAtHome: Yes, I was trying to work out what term was used and you're right it should be called a constant!

When I try to add the table in without any linking I get a message about an ambiguous outer join when I try to run the query. I'm more familiar with SQL (from a long time ago, so a little rusty) and would use a variable in that which I'd update in the script.

I'll have a look at entering controls on forms to see if I can figure it out.

 

[scoggy]

@GalaxiomAtHome:
Your comment about how I should be able to do this encouraged me to experiment, and I found that if I try it in a simple query with no other joins it works. Thanks!

 

[Galaxiom]

Queries having no joins are sometimes called Cartesian joins. One record is created for every possible combination of records. If both tables have a considerable number of records then a lot of combinations will be created.

But with just a single record in one of the tables it simply contributes its values once for each record in the other tables.

 

[scoggy]

Great - thanks for the clarification!