Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 27 votes, 5.00 average. Display Modes
Old 05-20-2005, 05:03 AM   #1
Rebel
Registered User
 
Join Date: May 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Rebel is on a distinguished road
Can an Append Query create new columns?

Hi, everyone. I've been reading this forum for several days now and the information I have picked up here has been wonderful. Thanks. Unfortunately, I haven't been able to find what I'm looking for on a problem I'm having. I apologize for the long post, but I wanted to include as much information as possible. Thanks for taking the time to look at my problem.

My Background: I am a Computer Managed Maintenance System Planner for a foundry. I am responsible for the administration of all maintenance and repair records for the plantís mobile equipment (dump trucks, fork lifts, etc.) Iíve been using Access as a report writer for several years. I have pretty good understanding of report writing and basic query writing, although I have never used pivot tables or crosstab queries, and I have only used amend/update queries a few times. I have a noviceís understanding of relational databases and I recently picked up a book on VB for MS Applications and have been devouring it. The rest of my limited VB knowledge comes from reading these forums, so my programming knowledge is still in its infancy.

What Iím Using: I am on a network that uses Windows 2K, to which I have only regular user privileges. We use an Oracle database (it is used for everything in the plant) with a third party CMMS. I use Access 2002 to do my report writing. I have a small database set up that contains links to the necessary tables in Oracle and a few tables I created to help ďmassageĒ my data for queries and reports.

My Problem: Our CMMS app is only set up to retain the current and the previous hour meter/mileage readings. I need to be able to retain all my hour/mileage readings, however, so that I can track up/down time, utilization, etc., over several different periods of time, including years.

What I Want To Be Able To Do: I want to archive my hour/cycle readings in a table so that I can reference them for later queries and reports. A couple of typical questions I want to be able to ask of this information are
1) ďWhat was the last known hour/mileage reading for this/all equipment on <date>?Ē
2) ďHow many hours/miles have passed between <date1> and <date2>?Ē

What I Have Done So Far: Iíve done research into crosstab queries and pivot tables. I donít fully understand these, but they donít seem to be what Iím looking for. I could be wrong, though. Then I got to thinking that the easiest thing for me to do might be to make a button on my main form that would run a query to append the hour data from the db to a table I made to store the info (tblMeterArchive). The table would have a column for the Equipment# and then the append query would create a new column with todayís date as the heading and dump the data into it. The problem is I canít figure out how to get a query to add a column to a table and stick todayís date in the heading. Iíve searched these forums and Iíve done a Google, to no avail. I have even taken time to just start going through every thread to see if this sort of thing has been discussed before. So far I havenít found anything and my boss is getting a bit peeved at the hours I have spent working on this so far this week.

So, my question is can an append query create new columns in a table? Or, am I barking up the wrong the tree and there is a better/easier to accomplish my goals? Even a link or pointer to the proper way to search for the information I need would be much appreciated.

Thanks again for taking the time to look at my problem.

Rebel is offline   Reply With Quote
Old 05-20-2005, 05:52 AM   #2
DJN
Guest
 
Posts: n/a
An append query can't create a new field for you. I suggest you create a new field in you archive table, ArchiveDate. Set it to Date/Time and format as you want. In the main table, create a new field, ArchiveDate. Set that to Date/Time, same format. Now, in the Default value field put Date() Create your append query and test it. It should put the current date into the ArchiveDate field.

INSERT INTO tblMeterArchive ( EquipmentNo, ArchiveDate )
SELECT Main.EquipmentNo, Main.ArchiveDate
FROM Main;
  Reply With Quote
Old 05-20-2005, 05:56 AM   #3
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
What would you do after you reached the limit of 255 fields?

Mile-O is offline   Reply With Quote
Old 05-20-2005, 07:15 AM   #4
Rebel
Registered User
 
Join Date: May 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Rebel is on a distinguished road
Quote:
Originally Posted by DJN
An append query can't create a new field for you. I suggest you create a new field in you archive table, ArchiveDate. Set it to Date/Time and format as you want. In the main table, create a new field, ArchiveDate. Set that to Date/Time, same format. Now, in the Default value field put Date() Create your append query and test it. It should put the current date into the ArchiveDate field.
HmmmÖ If I understand you correctly, what you are saying is have tblMeterArchive with three fields (Equip#, MeterRead, ArchiveDate) and just keep tacking the new meter data onto it? For instance, thisÖ


Equip# | MeterRead | ArchiveDate
12345 | 65.5 | 5/2/05
12346 | 1288 | 5/2/05
12347 | 65498 | 5/2/05


would become thisÖ

Equip# | MeterRead | ArchiveDate
12345 | 65.5 | 5/2/05
12346 | 1288 | 5/2/05
12347 | 65498 | 5/2/05
12345 | 70.8 | 5/16/05
12346 | 1491 | 5/16/05
12347 | 65754 | 5/16/05

at the next archive point. Is that correct? Iíll try this out and see how it works for me. My only problem is that I am unable to add any fields to the main Oracle db. Should I just create another table in Access, or would it be better to do this within the query?

Thanks for your help.
Rebel is offline   Reply With Quote
Old 05-20-2005, 02:23 PM   #5
DanG
Newly Registered User
 
DanG's Avatar
 
Join Date: Nov 2004
Posts: 477
Thanks: 1
Thanked 1 Time in 1 Post
DanG is an unknown quantity at this point
Why not have 2 tables..
1. Equipment (eq description...)
2. EquipDetail (Meter info with date of read)

The 2 tables are linked by equip# then you just update table2 using rows instead of the limitation of fields.
DanG is offline   Reply With Quote
Old 05-21-2005, 06:22 AM   #6
Rebel
Registered User
 
Join Date: May 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Rebel is on a distinguished road
That's what I'm pretty much trying to do, Dan. The Oracle db where the original tables reside stores all the Equipment info in one table with a field for MeterReading (current reading) and LastRead (the reading before the current one). It only stores those two readings, but I want to keep all of the readings. I have no privilages to create or change tables in the Oracle db, so I need to extract the information into the Access db I have created on my machine.

Per DJN's suggestion I'm trying to get my db to do what you are talking about. I got the basic table and query set up, but I'm having trouble getting Date() entered into the ArchiveDate field. Unfortunately, I ran out of time Friday to finish it up, so it'll be back at 'er on Monday morn.
Rebel is offline   Reply With Quote
Old 05-21-2005, 07:36 AM   #7
RV
Registered User
 
Join Date: Feb 2002
Location: UK
Posts: 1,115
Thanks: 0
Thanked 0 Times in 0 Posts
RV is on a distinguished road
Quote:
It only stores those two readings, but I want to keep all of the readings.
It surely stores all the historical data and my guess is it actually uses 2 tables.
So why would you have to create your own tables in Access?
All you need is a report.
Have you checked whether the software comes with a standard report that matches your criteria?

RV

RV is offline   Reply With Quote
Old 05-23-2005, 04:27 AM   #8
Rebel
Registered User
 
Join Date: May 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Rebel is on a distinguished road
Quote:
Originally Posted by RV
It surely stores all the historical data and my guess is it actually uses 2 tables.
Negatory, there, Rubber Duck. I can assure that it does not store any historical meter data. I just double checked with the admin.

Quote:
Originally Posted by RV
Have you checked whether the software comes with a standard report that matches your criteria?
Yes, I have; no, it doesn't.
Rebel is offline   Reply With Quote
Old 05-24-2005, 09:20 AM   #9
Rebel
Registered User
 
Join Date: May 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Rebel is on a distinguished road
Success!

Well, after rasslin' with it all day today, I finally got the Archive table and base query set up to do what I want it to do. Eureka! The Archive table is holding all the meter readings and the Append query is adding all the new readings properly. Once my data was all installed I tried a cross-tab query (my first real foray into c-t queries, by the way) and it looks exactly as I want it to: a row for each equipment# and columns for each date with the meter reading in it.

My next step is to figure out all the possible ways to use this data and write up some reports to take advatage of it. Then I can make it all a little more sophisticated with its own form and some fancy buttons and combo boxes.

I would like to say a big hearty "Thank you!" to this board, which has given some wonderful insight into Access and VB in general; to everyone in this thread for asking questions and making suggestions; and especially to DJN for setting my feet on the right path.

Rebel is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query Key Violation sudo Queries 4 07-10-2011 03:09 AM
Running Sum Query to create Graph / Chart Cosmos75 Queries 35 06-29-2010 05:54 AM
append query condundrum joolsUK0575 Queries 1 01-28-2005 09:14 AM
2 Append query problems Sam Summers Queries 2 02-27-2003 02:27 PM
Append to an Append Query?? PaddyIrishMan Queries 2 01-06-2003 12:49 AM




All times are GMT -8. The time now is 04:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World