SELECT Today

[smeco]

I have the following:

TABLE A
- Amount
- Date

TABLE B
- Amount

Table B has a list of amounts which really should have Today as Date

how do I create a union query in order to do that?
I guess I need something like:

select amount, date from A
union select amount, today() from B

however I haven't managed to find out what the exact syntax is

thanks in advance
smeco

 

[jsi]

Hi

Do you mean that every single record in tableB should have today's date against it?

You appear to have a poor table strucure (although you may of course be pseudo coding and leaving out detail )
Can you give some more info as to what you are trying to achieve?
Cheers
Jim

 

[smeco]

it's a list of money that is supposed to arrive at certain dates
for some there is an expected date (Table A)
some others are very close to arriving, and the list of those is in Table B

when I want to see the sums of money that should be arriving within the next month, I want all the money from Table B, and the money in Table A where the date is in the next month

hence I though of solving that with select amoun, today() from B

i hope that was clear
smeco

 

[smeco]

does anybody know?!

 

[Joe4]

smeco,

I have read your post a few times, and it is still unclear. Does Table A represent amounts that have already been received, and Table B represent amounts expected to be received? If so, wouldn't it be better (normalized)to create one table, with fields like:

Amount
Expected Date
Received Date

Then you could use queries to separate it however you like.

Also, does Table B have a date field? In your first post, you seem to make reference to that fact, but do not have that field listed under your Table B fields.

 

[smeco]

Forgive me for not being clear, English isn't my first language
I'll try and explain everything again, using a different example which is perhaps more 'visualizable'

There are people who come to my place from far away, and it takes them 2-3 days

I have TABLE A with:

NAME
CURRENT SITUATION
EXPECTED ARRIVAL

CURRENT SITUATION can be 'expected' or 'traveling'

if I expect John to arrive in November, I'll have: John - Expected - November
when John actually leaves to get here, his situation changes to: John- Traveling (no matter what EXPECTED ARRIVAL says)

so if I want the number of people to expect in the following months, I can group dates by month and query the table with WHERE CURRENT SITUATION = 'expected'

however, in the current month I also want to include all the people with 'traveling'


how do I do that? (I hope that was clear! if not please tell me)

my idea was:

SELECT name, today()
FROM A
WHERE SITUATION='Traveling'

UNION SELECT name, expected arrival
FROM A
WHERE SITUATION = 'Expected date'

and then count and group by month


thanks again

 

[Norie]

I'm pretty sure you don't need a UNION query.

Try an Iif statement or Nz statement

eg

MonthExpected:Month(Iif(IsNull([expected arrival], Now(), [expected arrival]))

or

MonthExpected: Month(Nz([expected arrival], Now()))

And the SQL would look something like this

SELECT A.name, A.situation, Month(Nz([expected arrival],Now())) AS MonthExpected
FROM Table1
GROUP BY A.name, A.situation, Month(Nz([expected arrival],Now()));

 

[Joe4]

so if I want the number of people to expect in the following months, I can group dates by month and query the table with WHERE CURRENT SITUATION = 'expected'

however, in the current month I also want to include all the people with 'traveling'

If that is the case, it sounds to me like you really don't care what the situation is, you simply want to include the people with a certain month in the "Expected Arrival" field.

So, I don't think you need to worry about/include what the "Current Situation" is, unless you may have values other than "Expected" or "Traveling" in that field.

Is that correct?

 

[jsi]

it sounds to me like you really don't care what the situation is, you simply want to include the people with a certain month in the "Expected Arrival" field.

That's what it sounds like to me too. I would just make 1 point at this juncture.
Never call an object (table, column, control et al) Name This is one of many reserved words in Access and should never be used. Another example would be Date

One thing I also noticed from Norrie's example code was that the table Alias appears to be missing, therefore the FROM clause would look something like

FROM Table1 As A

Just my 2c

Jim

 

[Norie]

One thing I also noticed from Norrie's example code was that the table Alias appears to be missing, therefore the FROM clause would look something like

Just a typo Jim.