UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cannot Append Missing Records Due To Single Null Field Available, Access 2016    
 
   
icerebro
post Dec 29 2017, 11:23 AM
Post#1



Posts: 2
Joined: 29-December 17



Hello all,

How can I append missing additional details for items 13,14,15 in 'main' table using as the criteria 'large' to pull this data from 'Data_to_append' table? ( I attached a sample database to illustrate my problem ). I cannot use a simple append because 'main' only has a single null field available for appending data.

I hope this makes sense. If not please let me know and I will try to find a way of rewording the questions. Please open the file and you will see what I am trying to accomplish.

Thank you so much for your help. I have been fighting with this one for a while.

Attached File  ForumQ1.zip ( 19.71K )Number of downloads: 2

This post has been edited by icerebro: Dec 29 2017, 11:25 AM
Go to the top of the page
 
theDBguy
post Dec 29 2017, 12:05 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,721
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

"Appending" data means "adding new records" to a table. It sounds more like you want to "Update" existing records containing blank or null values with information from another table. Is this correct?

If so, you have three records with missing values in the main table and yet have nine possible values to use from the other table. How do you determine which ones you want to use for the missing values?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
icerebro
post Dec 29 2017, 12:26 PM
Post#3



Posts: 2
Joined: 29-December 17



Hello ThedbGuy,

I understand my mistake. I updated the file to explain my problem better.

Attached File  ForumQ1__updated_.zip ( 20.43K )Number of downloads: 4


THis is like a cooking recipe. 'Main' is telling you to make a 'Large' and the append table has all the ingredients for that 'large'. I need to include all ingredients for a 'Large' in the 'Main' table.

Sorry about the confusion.
Go to the top of the page
 
theDBguy
post Dec 29 2017, 01:15 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,721
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Unfortunately, I still think you're talking about updating rather than appending data from one table to another.

However, although what you want to do can be easily done with the sample file you provided, you will run into bigger problems once the tables are populated with more data because there is no proper relationship between the two tables. When you link both tables using the value "Large" in the Type field, which by the way is a *reserved* word in Access and should be avoided, the records will match all of them instead of just one.

How about explaining what you're trying to model with your database, and maybe we can help you create a better table structure than what you're currently trying to use.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Dec 29 2017, 01:31 PM
Post#5



Posts: 2,426
Joined: 27-February 09



I beg your pardon, but what you've shown isn't really like a cooking recipe. You don't have any quantities of product. If you wanted to crank out X batches, you'd just multiply all the quantities in the recipe by that number. And you can do that in a query... no need to store it at all. (Well, if you wanted to, you could store something like (RecipeID, NumberOfBatches). and then just join the two and multiply in a query.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th July 2018 - 02:08 PM