Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting a field form my query into my database table

Posted on 2009-07-14
13
Medium Priority
?
175 Views
Last Modified: 2012-05-07
I created a query that found the date difference between two fields of my original table. I would like this date difference field to go into my original table as a new field/column - how would I go about doing this? Is the query necessary - is there someway I can paste this datediff formula into my Access table?
0
Comment
Question by:MaeMcG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24850400
In Access add another column to your table in Design View and then under properties for that column, under DEFAULT enter the formula to calculate the difference between the 2 columns in the same table. Eveytime yuo insert or update the fields the new column should update automatically.
P.
0
 

Author Comment

by:MaeMcG
ID: 24850546
Thank you so much for the quick reply - but unfortunately when I click properties under design view I only get "Default View" and do not just get "Default" (I am using Access 2003)... Also, is this the correct formula:
=DateDiff("d",[Date Opened],[Auto Report Date])

If those are the two fields I am looking to find the date difference between?

THANK YOU!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24850681
Yes, it is infact called "Default Value". Sorry for not being clear on that. Please insert your formula there and hopefully that should do it.
And yes, the formual looks fine.
P.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MaeMcG
ID: 24850723
Pssandhu - thank you, I will try that now. For my new column should I put the "Data Type" as a Date/Time, Number or Text? Thanks
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24850749
I think number should be fine here. Also, if this does not work, we will have to create a number column of type number and then write an UPDATE query to update the value in that table. So let me know how it goes.
P.
0
 

Author Comment

by:MaeMcG
ID: 24850763
This screenshot (see attachment) keeps appearing when I do what you say... thanks again.
screenshot.JPG
0
 
LVL 17

Accepted Solution

by:
pssandhu earned 1500 total points
ID: 24851032
Hmm.. I had a feeling that it might error out. Alright so leave the new column that you added in but take out the formula in the Default Value box and save the changes to the table.
Then write an UPDATE query like this. This will be written in "SQL View". To get here create a query, right clicl on the header of the query window and select "SQL View".
UPDATE [AUDIT ISSUES]
SET [AUDIT ISSUES].[DAYS TO TARGET DATE] = DATEDIFF("d",[AUDIT ISSUES].[Date Opened],[AUDIT ISSUES].[Date Closed])
I am assuming your table name is ADUTI ISSUES. Please correct any column or table name errors.
P.
0
 

Author Comment

by:MaeMcG
ID: 24851254
I pasted exactly what you said on the SQL View... do I need to have SELECT [Audit Issues] in first and then paste what you told me?

This did not update my fields in my original Audit Issues table, instead it created a column/field named "DAYS TO TARGET DATE" in the Query Datasheet View?

Thanks P.
screen-shot-2.JPG
0
 

Author Comment

by:MaeMcG
ID: 24851296
What I mean to say is that it does not do any calculation, but whatever I do type into Days to Target Date will autopopulate my field.

Thanks!
0
 

Author Comment

by:MaeMcG
ID: 24851399

SELECT [Audit Issues].[Auto Report Date], [Audit Issues].[Date Opened], DateDiff("d",[Date Opened],[Auto Report Date]) AS [Days To Target Date]
FROM [Audit Issues];
UPDATE [AUDIT ISSUES] SET [AUDIT ISSUES].[Days To Target Date] = DateDiff("d",[AUDIT ISSUES].[Date Opened],[AUDIT ISSUES].[Date Closed]);
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24852208
First of all, when you run an update query you should get a pop up window saying "You are update these manu records" or somehting along the same line. You should not be seeing a datasheet view after you run the update query; all you will see are the pop windows as I described above.
And did you add the new column to the table without the default value? If you did then you should see an extra column with no values in that table when you open the table. The column should be of NUMBER datatype.
Also, are all the three column on the same table? What I mean is your "Date Opened", "Date Closed" and "Days to TargetDate" all on the same table? If not can you please explan what is the source table and how are the source and destination tables linked together.
No, you do not need a select statement before the update statement. The update statement should be al by itself.
Sorry for the delayed reply, I was stuck in a meeting.
P.
0
 

Author Comment

by:MaeMcG
ID: 24853952
How do I do the default value again - and how should I link the tables?

My original table is AUDIT ISSUES and I added a column/field "Days to Target Date"... I want the amount that my query (the one you gave me) to autofill or autopoulate into that column.

Thank you very much - and not a problem about being in the meeting. I'm a beginner here, so I appreciate your time and efforts in walking me through this.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24859488
MaeMcg,
Can you please reply back to my questions I asked above before we can proceed any further? I am reiterating them below:
1. Are all the three column on the same table? What I mean is your "Date Opened", "Date Closed" and "Days to TargetDate" all on the same table?
2. If not, then can you please explan what is the source table and how are the source and destination tables linked together.
Also, can you please give me the exact names of the tables and column we are dealing with so that when I provide you with a solution, you will just have to copy paste?
Thanks
P.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question