Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to update table

Posted on 2011-10-06
11
Medium Priority
?
225 Views
Last Modified: 2012-06-27
hello - i have attached sample database.
database has two tables [tblUsage] and [tblUsageInventory]
[tblUsageInventory] has 4 fields [tblUsage] does not have- Stnd Cost-User-Category-Parts Categories
Everyday [tblUsage] gets has its data replaced with new data - by automated service.
( * sometimes the number of records changes on the automated import process* )

The [tblUsageInventory] - users are adding data into the 4 fields( that are not in tblUsage) from a form daily.

question:
how can i update [tblUsageInventory] with new data  from [tblUsage] everyday?
or I should say just 1 day and I will then be able to figure out an automated process.
thank you
TestData.accdb
0
Comment
Question by:davetough
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36927512
Can I ask why this system is designed in this way...?

In a typical Database data is not "Replaced", rather new data is "appended".
0
 

Author Comment

by:davetough
ID: 36927755
I think you mean by appened- added on to data already there- if i am correct-
reason is: because usage figures needs to be current- how many of this is in this place and how many in other place etc...- the real tables have more fields-
not sure that is good enough explanation to clarify -
the if a record is in the tblUsageInventory- and the Usage table after having data replaced does not have that record- then that record is not longer needed-
I may have to restate this whole question - if this is not clear
thank you
0
 

Author Comment

by:davetough
ID: 36927766
I also did not clarify another database system provides this updated tblUsage data
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 36928321
OK, so you posted the DB as-is.

Can you post a copy of the DB *exactly* as it should appear after the 'update'?
0
 

Author Comment

by:davetough
ID: 36928396
i attached database

for example - if tblUsage is mondays data- then tblUsageInventory should look like tblUsageAfterUpdate ( poorly named examples)

if tblUsage2 is tuesdays data- then tblUsageInventory should look lik tblUsageAfterUpdateWithOne less record.
TestData.accdb
0
 

Author Comment

by:davetough
ID: 36928427
well i didn't explain that right-
tblUsage may change the data in tblUsageInventory-

in my examples all data is the same-
here is my last attempt

tblUsage arrives each day- and that data has to replace data that matches in tblUsageInventory-
( exception- if there are records in tblUsageInventory that do not match- they are deleted)
I am sure i am making this too complicated- i apoligize
0
 

Author Comment

by:davetough
ID: 36928438
matches Item code-
if you think i am confused now - i am -
i am going to give you points for this fiasco- if you can seen any solution or ideas- they will be apreciated- if not - i hope you don't hold this question against me in the future-
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36928455
Sorry, now I am totally confused...

You want 5 tables after the update???

I need to see the DB *EXACTLY* as it needs to be after the update...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36928489
No need for points if no solution is acceptable...

I am just confused by this process.

I have never seen an Inventory system that needed to be updated in this manner.

Perhaps I am not understanding the full scope of this application.

How much of this system is "etched in stone".
Are you quite sure this is the most efficient system for doing what you need?
0
 

Author Comment

by:davetough
ID: 36930167
last night through trying to explain this- i realized solution:
tblUsage new data is imported in at 6:00 am in the morning-
2 step process
1. run a make table query that creates a new table from tblUsage called tblUsageNew-
2. run a make table query that joins tblUsageN and tblUsageInventory that replaces and creates new tblUsage.

checked out process - it works-
by sending you that second database- i led you to believe i need 5 tables updated-
my error
I need to think through my problems more thoroughly - before asking a question - that is unclear
thanks

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36937574
Great, then click the "Request Attention" link and ask that your own post be accepted as the solution.

This is that so other members searching for the same issue will see the actual solution.

;-)

Jeff
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

578 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