Date Add question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have to take the date 'Receipt Date' from one table (T1) and then add number
in another field 'Collection Schedule' in another table (T2).

But it does not stop there. I also need to show a full range of what the
dates will be if the 'Collection schedule' is multiplied between 1-12.

Reason - I have some 'Collection Schedules' at 1 (every month) and others
upto 12, and I want to see the collections needed over a 12 month period.

Cheers
 
Show us a SQL statement of how you would link the two tables together now
along with the primary and foriegn keys.
 
Jerry

A bit of a novice, so apologies if what I write is off the mark.

Table 1 (Receipts) and Table 2 (Companies) are linked by the automatic
primary key 'CompanyID' from T2.

T1 has a auto primary key as ReceiptsID, but this is not used anywhere else.

Not sure how I show the SQL statement....
 
Pardon me for butting in...
I'm going to take a stab at this
even though I may not fully understand...

create a table ("tblNum")
w/one Long field ("Num")

tblNums
Num (type Number)
1
2
3
4
5
6
7
8
9
10
11
12

Show your 2 tables in query designer.

Right-mouse click on Receipts table
and choose Properties.
Set Alias to "T1"

Right-mouse click on Companies table
and choose Properties.
Set Alias to "T2"

In T1 drag-and-drop field CompanyID
over on T2.CompanyID. You should now
have a join line that connects them.

Double-click on each field in each table
that you want to see in the query results
(so they will be added to query grid).

Go to SQL View, your SQL should look
something like:

SELECT
T2.CompanyID,
T2.CompanyName,
T2.[Collection Schedule],
T1.ReceiptsID,
T1.ReceiptDate
FROM
Companies As T2
INNER JOIN
Receipts As T1
ON
T2.CompanyID = T1.CompanyID;

Go back to Design View and
add tblNums without joining it
to either table.

Give it an alias of "N"

In the next empty column of the grid

Field: Factor: N.Num*T2.[Collection Schedule]
Table:
Sort:
Show: <checked>
Criteria:
Or:

Now, here is where I am confused....
Do you want to add (Factor months) to ReceiptDate?

If so, in next empty column of grid

Field: FactorDate: DateAdd("m",[Factor],T1.ReceiptDate)
Table:
Sort:
Show: <checked>
Criteria:
Or:

if you only want to "see" next 12 months of data,
I imagine you would add to Criteria row in col above

Field: FactorDate: DateAdd("m",[Factor],T1.ReceiptDate)
Table:
Sort:
Show: <checked>
Criteria: <=DateAdd("m",12, Date())
Or:

Of course, I may have completely misunderstood...
 
Hi,

Just in case Gary's excellent suggestion doesn't work, you can see the SQL
by opening the query in design view.

Next go to View on the menu and drop down to SQL View. That's what Access is
actually running for the query. Creating queries in the QBE window then
switching over to SQL view is how I got started in learning SQL which is a
very good thing to know.

The information you already provided on primary keys and relationships is
also very helpful on future questions.

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
 
Back
Top