Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-07-2019, 11:32 AM   #1
mikke3141
Newly Registered User
 
Join Date: Oct 2019
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
mikke3141 is on a distinguished road
Append multiplying generated records

Hi,

I'm trying to insert into my headcount table new persons with the following code.

Code:
INSERT INTO HeadCountTbl (HeadCount, EmpID, Month_ID )
SELECT 1, HeadCountTbl.EmpID, HeadCountTbl.Month_ID
FROM MonthTbl INNER JOIN HeadCountTbl ON MonthTbl.ID = HeadCountTbl.Month_ID
WHERE (((HeadCountTbl.EmpID)=123) AND ((MonthTbl.Month)=201905));
The month information is coming from another linked table. Now each time I run the code it doubles the amount of records appended. First time it adds one person, second time the same person twice and third time four times. Is there a way to fix the SQL so that it generates only one line to the table?

mikke3141 is offline   Reply With Quote
Old 11-07-2019, 11:37 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,727
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Append multiplying generated records

Hi. What happens if you don't join the month table with the head count table?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-07-2019, 11:46 AM   #3
mikke3141
Newly Registered User
 
Join Date: Oct 2019
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
mikke3141 is on a distinguished road
Re: Append multiplying generated records

Hi,

Then the Month_ID field in the headcount table becomes blank.

Mikke

mikke3141 is offline   Reply With Quote
Old 11-07-2019, 11:50 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,727
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Append multiplying generated records

Quote:
Originally Posted by mikke3141 View Post
Hi,

Then the Month_ID field in the headcount table becomes blank.

Mikke
Hi. Probably not the best way but try using a DLookup() for it.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   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
Need to ignore Microsoft Access can't append all the records in the append query DNASok Queries 7 03-21-2013 06:22 AM
Between Dates Parameter Query - Extra Records Generated?? CarolW Queries 4 04-05-2008 12:21 PM
MS access can't append all the records in the append query japi_s General 3 02-19-2007 06:09 AM
[SOLVED] Multiplying Field Values Across Records jkthomp Reports 1 08-23-2004 11:18 AM
append query is multiplying records carpstar Queries 1 08-15-2002 07:41 PM




All times are GMT -8. The time now is 08:21 AM.


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