Append query

[Guest]

Hello,

I am currently developing an append query to add a new record with a new
date. That works fine. I have two problems:

1) The major problem I have is that if a set of records are unique in
someway, then it will replicate all the rows. For example, my own working
hours records means I may do different hours from week to week so it will
replicate all distinct rows. I am currently using the "DISTINCT" command.
Will excluding this sort this problem out?

2) The minor query I have is that an append query throws up the warning
message about adding new records. Is it possible not to have this message?

cheers in advance.

 

[Guest]

Suba,

1) Without seeing your table structure and the query I am not sure how to
help.

2) Before your procedure put in the following DoCmd.SetWarnings False
After it is done, turn the warnings back on: DoCmd.SetWarnings True

hth

Vanya

 

[Guest]

Hello,

Not sure about (2). Where do I find the "DoCmd" command?

As far as (1) goes, I have a 1-to-many relationship so each employee has
weekly records. For example if a person does 36 hours a week, every week
then, at the moment, it will only add one record. For some people the hours
can change from week to week so it will add a new number of rows depending on
how many unique rows there are. If someone does 31, 36, 36, 32, 18, 15, 36
over a seven week period then the query will add 5 new rows. I only need one.

I have tried using a button with a "duplicate record" option but it can't do
what I need. Here is the query.

INSERT INTO Department ( EmployeeID, WeekID, Dept, Subdept, Costcentre,
Rate, Contracthrs, timehalfhrs, doublehrs )
SELECT [Department.EmployeeID], DMax("[WeekID]","Department")+7,
[Department].[Dept], [Department].[Subdept], [Department].[Costcentre],
[Department].[Rate], [Department].[Contracthrs], [Department].[timehalfhrs],
[Department].[doublehrs]
FROM Department
WHERE [Department].[EmployeeID]=[Forms]![Employee].[EmployeeID];

 

[Guest]

Scuba,

Sorry but I was on holiday. Last night at home I answered you but I
guess I forgot to post it. Sorry. I'll try again tonight.

As for the DoCmd, that is in VBA connected to the button. So if your
query is called qry_Insert_Hours2Department (or whatever you called it) on
the command button you would

DoCmd.Setwarnings False
stDocName = "qry_Insert_Hours2Department"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

Vanya

Hello,

Not sure about (2). Where do I find the "DoCmd" command?

As far as (1) goes, I have a 1-to-many relationship so each employee has
weekly records. For example if a person does 36 hours a week, every week
then, at the moment, it will only add one record. For some people the hours
can change from week to week so it will add a new number of rows depending on
how many unique rows there are. If someone does 31, 36, 36, 32, 18, 15, 36
over a seven week period then the query will add 5 new rows. I only need one.

I have tried using a button with a "duplicate record" option but it can't do
what I need. Here is the query.

INSERT INTO Department ( EmployeeID, WeekID, Dept, Subdept, Costcentre,
Rate, Contracthrs, timehalfhrs, doublehrs )
SELECT [Department.EmployeeID], DMax("[WeekID]","Department")+7,
[Department].[Dept], [Department].[Subdept], [Department].[Costcentre],
[Department].[Rate], [Department].[Contracthrs], [Department].[timehalfhrs],
[Department].[doublehrs]
FROM Department
WHERE [Department].[EmployeeID]=[Forms]![Employee].[EmployeeID];

Suba,

1) Without seeing your table structure and the query I am not sure how to
help.

2) Before your procedure put in the following DoCmd.SetWarnings False
After it is done, turn the warnings back on: DoCmd.SetWarnings True

hth

Vanya