I am running an append query using query by example (but included equivalent
SQL code). The query counts the number of bookings and appends this number
to bookings to Tbl_Weekly
INSERT INTO Tbl_Weekly ( NoofBooks, StartDate, EndDate )
SELECT Count(Bookings. DateofEvent) AS Noofbook, Forms!Printouts !WSdate AS
WeekStart, Forms!Printouts !WEdate AS WeekEnd
FROM Bookings
WHERE (((Bookings.Dat eofEvent)>[Forms]![Printouts]![WSdate] And
(Bookings.Dateo fEvent)<([Forms]![Printouts]![WEDate])))
GROUP BY Forms!Printouts !WSdate, Forms!Printouts !WEdate;
It works but...when the Count is 0 no record is appended. However I want to
append a zero count as well (ie 0,StartDate, EndDate). Any ideas?
Any help would be greatfully received
Regards
Geoff
1  1571 
Geoff, what happens if you drop the GROUP BY clause?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Geoff" <gf****@freenet name.co.uk> wrote in message
news:fJ******** *************** *******@brightv iew.com... I am running an append query using query by example (but included
equivalent SQL code). The query counts the number of bookings and appends
this number to bookings to Tbl_Weekly
INSERT INTO Tbl_Weekly ( NoofBooks, StartDate, EndDate )
SELECT Count(Bookings. DateofEvent) AS Noofbook, Forms!Printouts !WSdate AS
WeekStart, Forms!Printouts !WEdate AS WeekEnd
FROM Bookings
WHERE (((Bookings.Dat eofEvent)>[Forms]![Printouts]![WSdate] And
(Bookings.Dateo fEvent)<([Forms]![Printouts]![WEDate])))
GROUP BY Forms!Printouts !WSdate, Forms!Printouts !WEdate;
It works but...when the Count is 0 no record is appended. However I want
to append a zero count as well (ie 0,StartDate, EndDate). Any ideas?
Any help would be greatfully received
Regards
Geoff This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: PMB |
last post by:
Thank you in advance for any and all assistance.
I'm trying to use a make table query to pull the last transactionID, so I
can use an append query to reset the transactionID to the next sequential
number. My problem is, the make table query is taking all the
TransactionID's and putting them in the new table.
Is there a way to take the last transactionID only and put it in the new
table? So this way, when I clear the old Transactions,...
|
by: |
last post by:
I found similiar issues in MS-KB but nothing that helped
me; got the Windows and Office updates from the MS website
but that hasn't changed the behavior of this problem, and
I don't see anything relevant in the newsgroup; help will
be gratefully received.
Windows 2000 system, I'm logged in with admin rights, using
Office 2003. I have two databases on my local drive, in
the same subdirectory. In the database HAMCHEESE, I create
an...
|
by: Aaron |
last post by:
Hello fellow programmers,
I am trying to run an append/update query from code, a command button
on a form initiates the queries.
the format i am using is;
_____________________________________________________
SELECT "criteria"
|
by: Kristina |
last post by:
I'm trying to run multiple append queries in an Access front end that
append data from tables in a .mdb into tables in SQL Server 2000. My
Access front end has a link to both tables, the .mdb and the SQL
Server. What happens is that I run the first query, and it runs
without problem. When I run the second query, I get an error that
there is a key violation on every record in that table. However, if I
exit my front end application,...
|
by: David Barger |
last post by:
Greetings,
It appears that an Append Query I run in Access XP is randomly failing
to append a field.
I have payroll data being entered into a payroll database. This data
is exported daily to two csv files. (One for updated data, and the
other for unupdated data.)
The CSV files are attached to my Jobcosting database. After the CSV
| | |
by: Michael C via AccessMonster.com |
last post by:
Hello,
I have a table that I am appending 3 seperate tables into. My main problem
is that each time I append the data, it simply adds to the data already there.
That might sound ok, except that if I append the data 3 times in succession,
it copies the same data over 3x. Now I have copies in triplicate. It used
to only transfer records that weren't already there, but not anymore.
If I can't get the append to append correctly, I was...
|
by: James Hallam |
last post by:
I have a form with a subform. When there are no entries in the
subform, I have an append query which makes a default entry in the
subform (for what I am doing there needs to be at least one value in
the subform).
The code in the form is this:
Private Sub Form_AfterInsert()
If Me.strWholeStr = 0 Then
DoCmd.SetWarnings False
|
by: pmacdiddie |
last post by:
I have an append query that needs to run every time a line item is
added to a subform. The append writes to a table that is the source
for a pull down box. Problem is that it takes 5 seconds to run. This
makes adding lines to an order too slow for the users. The result of
the query provides real time availabilty, so I really do need this to
work.
The tblJobs has only 10,000 records, tblJobsLineItems has 150,000
records.
|
by: wade.wall |
last post by:
Hi all,
I am having a problem appending data to an existing table. I have two
tables with identical fields and I want to append the data from one
table (T2) to the first (T1). T1 has 136 records and T2 has 209, for
a difference of 73 records. When I run the append query, only 72
records are selected from T2, and there doesn't seem to be a pattern
as to why the records selected were selected. They aren't the last 72
records or the...
|
by: Scott12345 |
last post by:
Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and then opens up a form that displays all 30 values. The user can then go through these and change the ones they are responsible for. After they have updated this form, i have a delete query that searches for the dummy values and removes them.
My...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
