I have a make table query which makes a temporary table tblTmpCondemned. Since this tblTmpCondemned does not have a date field ,I would like to user to enter the date field, SptDate via a form (frmUserInput) together with the all the fields in the tblTmpCondemned to be appended to a new table (tblCondemned). I cannot group records in tblTmpCondemned if I were to include the date field in making a table .
So how do I populate the user input date (SptDate) into tblCondemned or tblTmpCondemned whichever is convenient. A set of records for tblTmpCondemned keep changing and after a certain period user enters SptDate via the form. Report can then be based on tblCondemned for certain dates
Making a tblTmpCondemned:
SELECT tblFrom.Item, Sum(tblTo.OldQty) AS SumOfOldQty, Sum(tblTo.SptQty) AS SumOfPhyQty INTO tblTmpCondemned
FROM tblFrom INNER JOIN tblTo ON tblFrom.InvID = tblTo.InvID
GROUP BY tblFrom.Item;
tblTmpCondemned should look something like this
Item SumOfOldQty SumOfSpoiltQty
A 20 2
B 40 5
C 70 3
The final result in tblCondemned should look like this
SptDate Item OldSum SpoiltSum
20/03/2008 A 20 2
20/03/2008 B 40 5
20/03/2008 C 70 3
I'm sorry but some of your explanations are not clear.
If the table tblTmpCondemned does not have a date column, as you stated at the beginning of your message, the question of knowing which table is convenient is irrelevant: adding SptDate to tblTmpCondemned is simply impossible without changing its structure.
Secondly, you do not explain how and when data from tblTmpCondemned is transferred to the new table tblCondemned, nor do you provide any explanation about the tblFrom and tblTo tables and their structures.
Could you please supply a kind of flowchart or logical sequence of the events in your application.
With what I know from your explanations so far, I guess that you could create a table with the input dates and find a way to link it to one of the other tables, or if the transmission of data is induced manually when the user enter a date, you could simply add this date (it could be the Now() function so you would not need to bother with getting the date in the form into a query) into your query.
Finally, the query in your message is a SELECT query, so it cannot be the one that populates the tblCondemned table. What about this one?
Thank you for the reply
Because of complication I will try to explain without adding more unnecessary info which may not affect what I am trying to achieve.
The only way I can save the sum of old qty and sum of spoilt qty in a subform is to group the items and create a make-table query and create tblTmpCondemned. The sum of old qty and sum of spoilt qty changes everyday in the subform. Therefore the set of records in tblTmpCondemned also changes.
I would be appending a set of records from tblTmpCondemned to tblCondemned so that these records are stored permanently and I can retrieve them at any time. TblCondemned will need dates when it is appended from tblTmpCondemned so that Access would know which items are old and spoilt on that particular dates.
I need to make a report every six months on the sum of old qty and sum of spoilt qty of each item which I can retrieve from tblCondemned. Unfortunately my tblTmpCondemned and my tblCondemned do not have dates.
If tblCondemned has dates, user needs to enter the date or range of dates to retrieve the appropriate records from tblCondemned to produce the report.
I hope this is clear.
First of all you need a place where to store a date. You can add a Date/Time column in the tblCondemned table or you can create a third table where you store the date. If you chose the latter option this table also have to contains a colum where to store a link to tblCondemned as a foreing key.
The date you want to store is the one when you append data to the tblCondemned table, so the Now() function will fit here.
Let's suppose you chose the first option (add a Date/Time column to the tblCondemned table that you name DateOfInsertion). Your append query may looks like:
INSERT INTO tblCondemned ( Col_1, Col_2, Col_3, DateOfInsertion)
SELECT tblTmpCondemned .Col_1, tblTmpCondemned .Col_2, tblTmpCondemned .Col_3, Now() AS DateOfInsertion
WHERE <any needed condition>
Now, every time you run the query, the current date will be inserted into the DateOfInsertion column of the tblCondemned table.
We shall first create a public variable that will keep the input date. To do so, create a module, or open an existing one, and in the declaration section, add:
Public UserInputDate As Date
When I say "a module", it must be an independant module, not a module behind a form: this is very important. As our UserInputDate variable is declared as Public it can be accessed from everywhere in the application.
In the same module, add a function like this:
Public Function GetUserInputDate()
GetUserInputDate = UserInputDate
On the form where the user is supposed to enter a date, create a textbox control, say Text_UserInputDate, set its Format property to a convenient value and add the following code in the AfterUptate event:
Private Sub Text_UserInputDate_AfterUpdate()
UserInputDate = Nz(Me.Text_UserInputDate.Value, UserInputDate)
We use the Nz function to prevent trying to assign a Null value to UserInputDate in case the user removes the contents of Text_UserInputDate (UserInputDate is declared as a Date type variable and Date type variables cannot have a Null value assigned to them: it triggers an error).
Note that we declared the GetUserInputDate as Public, so it's also accessible everywhere in the application.
Now the query might look something like this:
INSERT INTO Table2 ( Col_1, Col_2, Col_3, Col_DateTime )
SELECT Table1.Col_1, Table1.Col_2, Table1.Col_3, GetUserInputDate() AS DateOfInsertion
WHERE (((Table1.Col_2) Like "data*"));
The date generated in the tblCondemned is a text instead of date/time. How do you change data type of the DateOfInsertion to date/time so that I can use generate a query based of real date?