Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Streamline Database...

Status
Not open for further replies.

bluenoser337

Programmer
Jan 31, 2002
343
CA
I have a single Access '97 table collecting about 10,000 records a day. These records represent manufactured parts. At the end of the day, after printing a report giving the full parts listing for the day, I would like to summarize the data (many parts have exactly the same characteristics) and add it to a "summarized table". This table could then be queried with a "date range" search, etc to report "old" information without having to query every individual record at a later date. My question is...how do I "put" this data in the other table, make sure it is there, and then delete the records from the "daily" database. Yes, I'm green. Many thanks!!
 
As usual - there are many ways to skin the cat.
( But it still tastes like chicken )

My favoured approach is not available bacause you are on A97

So its probobly a matter of creating a GroupBy query that will summarise the data in the way you want

Then modify it to be a MakeTable query so that it creats a table with the required fields / types/ sizes etc.

Check this new table to make sure the field size definitions are future proof ( adjust sizes as necessary )

Then convert the query from a MakeTable to an Append query and run it as an append query in future.

To get rid of the data from the original table - ( Are you really sure you want to do this ? ) - you can use the WHERE clause from the original query to make a Delete query to do your dirty work.

Then on a Form you put a command button.
in the button's onClick event you put suitable code that check that the users really REALLY does want to do this and understands the implications

Then use DoCmd.RunSQL instructions that will execute the Append and Delete queries



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Thanks...LittleSmudge. I can upgrade to 2000 if I have to. What's your favoured approach?
 
Dim an ADODB.Recordset
Open an ADODB.Connection

Begin a Transaction on the ADODB.connection

Do all the steps above using rst.Open

Only when all of the steps are done do you COMMIT the Transaction


That way if you get a failure anywhere in any of the actions you can ROLLBACK the transaction and no work ( No damage ) has been done.


It's just my paranoya coming out you know.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Hey LittleSmudge...I too suffer from that paranoia...but with good reason (once bitten). I think I'll give this a shot!! I'll report back later. Many thanks from Canada!!
 
I'm using "DISTINCT" in my query to combine identical records, but one column in my table is "DateTime" which is messing this up. I only want the "Date", not the "Time" portion to be considered for the "DISTINCT" operation. What SQL code will do this for me. Many thanks...again!!
 
SELECT DISTINCT Int(DateTimeField) As TheDate FROM etc





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Awesome...but I can't change the format back to "date" in the report...can I?
 
YES,

In the report place a text box control and bind it to [TheDate] and set the FORMAT property of that text box control to "Medium Date" or whatever date format you want.

Dates are only stored as Integers anyway - times are stored as real number fractions of integers. so you are not changing the FORMAT of the data - just the quantisation.



'ope-that-'elps

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top