I have a customer table and each customer has an INDATE and an OUTDATE, I am
trying to create a report that shows all customers from INDATE and all
cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query
combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and I
get double records.
Smith
Smith
Jones
Jones
etc.
What am I doing wrong,? can SKS help please.
Gordon. 10 1555
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk... I have a customer table and each customer has an INDATE and an OUTDATE, I am trying to create a report that shows all customers from INDATE and all cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and I get double records.
Smith Smith Jones Jones etc. What am I doing wrong,? can SKS help please.
Gordon.
Have you tried joining query1 and query2 inside query3? Both queries contain
the CustomerID, from within query3 click and drag the CustomerID from query1
to the CustomerID in query2. Run the query3 to see if you get the correct
results. If not, post the SQL for all 3 queries and we'll have a look at
what's going wrong.
Jeff
Hi, Jeff,
This is the SQL before your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN], OUT;
This results in two records showing for the "IN" result and two records for
the "OUT"
The results are correct for any date I enter.
This is the SQL after your suggestion.
SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID,
OUT.Prefix, OUT.Surname, OUT.OutwardDate
FROM [IN] INNER JOIN OUT ON [IN].ID = OUT.ID;
I am only getting the column headers, no results.
Regards, Gordon.
----------------------------------------------------------------------------------------------------
"Jeff Smith" <No****@Not.This.Address> wrote in message
news:e1**********@lust.ihug.co.nz... "Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message news:e1*******************@news.demon.co.uk...I have a customer table and each customer has an INDATE and an OUTDATE, I am trying to create a report that shows all customers from INDATE and all cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and I get double records.
Smith Smith Jones Jones etc. What am I doing wrong,? can SKS help please.
Gordon. Have you tried joining query1 and query2 inside query3? Both queries contain the CustomerID, from within query3 click and drag the CustomerID from query1 to the CustomerID in query2. Run the query3 to see if you get the correct results. If not, post the SQL for all 3 queries and we'll have a look at what's going wrong.
Jeff
Gordon, you haven't made entirely clear, just what you need here.
See comments below -
Gordon Youd wrote: Hi, Jeff, This is the SQL before your suggestion. SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID, OUT.Prefix, OUT.Surname, OUT.OutwardDate FROM [IN], OUT;
This results in two records showing for the "IN" result and two records for the "OUT"
This is creating a Cartesian Product result. You will get one entry
from each input query for each in the other. Two records in each query,
4 results. Three records in each input query, 9 resulting records.
Four would give you 16, etc. The results are correct for any date I enter.
This is the SQL after your suggestion. SELECT [IN].ID, [IN].Prefix, [IN].Surname, [IN].InwardDate, OUT.ID, OUT.Prefix, OUT.Surname, OUT.OutwardDate FROM [IN] INNER JOIN OUT ON [IN].ID = OUT.ID;
I am only getting the column headers, no results.
Normally, or perhaps a better expression, commonly, when you want a
result from more than one input table or query you use a join to
establish the "relationship" between the two tables.
You haven't indicated if there is some sort of connection between the ID
in one table and the ID in the other. Regards, Gordon.
---------------------------------------------------------------------------------------------------- "Jeff Smith" <No****@Not.This.Address> wrote in message news:e1**********@lust.ihug.co.nz... "Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message news:e1*******************@news.demon.co.uk... I have a customer table and each customer has an INDATE and an OUTDATE, I am trying to create a report that shows all customers from INDATE and all cutomers from OUTDATE, where INDATEand OUTDATE =[]. (wildcard input date)
I have created INDATE as query1, OUTDATE as query2, I created a 3rd query combining both.
I run the 3rd query, put in the [] indate then [] outdate, press enter and I get double records.
Smith Smith Jones Jones etc. What am I doing wrong,? can SKS help please.
Gordon. Have you tried joining query1 and query2 inside query3? Both queries contain the CustomerID, from within query3 click and drag the CustomerID from query1 to the CustomerID in query2. Run the query3 to see if you get the correct results. If not, post the SQL for all 3 queries and we'll have a look at what's going wrong.
Jeff
I'm going to make a guess, that what you want is all of the records from
each of the two input queries and that there is no relation between the
IDs in the two queries. If that guess is correct, then what you need is
a UNION query. Something like this:
SELECT ID, Prefix, Surname, InwardDate as DDate
FROM Query1
UNION
SELECT ID, Prefix, Surname, OutwardDate as DDate
FROM Query2
See if this is any closer to what you need.
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Sorry about the sloppy way I put the problem to the group, I'll start
afresh.
I have only one main table of "Bookings".
Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate
and Outdate, which will be called by
wildcard date input.
From this I want to create a Report, showing Customers and Indate on the
left, Customer and Outdate on the right found by the wildcard date.
Hope that makes sense.
I appreciate the groups help.
The brain does not work too well when you get old. :-)
Regards, Gordon.
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in
news:e1*******************@news.demon.co.uk: Sorry about the sloppy way I put the problem to the group, I'll start afresh.
I have only one main table of "Bookings". Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate and Outdate, which will be called by wildcard date input.
From this I want to create a Report, showing Customers and Indate on the left, Customer and Outdate on the right found by the wildcard date. Hope that makes sense.
It doesn't. When you say "matching Indate and Outdate" do you
mean that indate is less than or equal to your wildcard and
outdate is greater than or equal to your wildcard, or do you
mean indate and outdate are equal, and also equal to the
wildcard?
if the first, you want a query that reads
SELECT * from Bookings
WHERE [input the date]
BETWEEN indate AND outdate.
if the second, make it
SELECT * from Bookings
WHERE [input the date] = outdate
AND [input the date] = outdate .
the third variant is if you want to match either the indate OR
the outdate
I think you can figure out which word to change in the second
query.
I appreciate the groups help.
The brain does not work too well when you get old. :-)
Regards, Gordon.
--
Bob Quintal
PA is y I've altered my email address.
Gordon Youd wrote: Sorry about the sloppy way I put the problem to the group, I'll start afresh.
I have only one main table of "Bookings". Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate and Outdate, which will be called by wildcard date input.
Bob's not the only one confused by this. I'll take another guess at
what you are trying to do.
You have one date that you input as a parameter. You are calling that a
"wildcard" date. You want to generate a report with a list of customers
whose Indate is equal to that date on the left side of the report. On
the same report, you want another list of customers, on the right side,
whose Outdate is equal to that same date. Hence, you could potentially
have customers who appear on both sides of the report.
Is this anywhere close to correct?
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Hi, Bob & Randy,
I want to find from the "Booking" table all "Customers" whose "Indate"
matches the "wildcard" input and all "Customers" whose "Outdate" matches the
same "Wildcard.
No "Customer" would ever have the same "Indate" and "Outdate".
I would have a list of customers coming "IN" and a list of customers going
"OUT on the same date.
I can then deal with both customers on the same day. The Report would show
the "INs" in the left hand column, the "OUTs" in the right hand column.
Does this help?
Regards, Gordon.
----------------------------------------------------------------------
"Randy Harris" <pl****@send.no.spam> wrote in message
news:X7******************@newssvr25.news.prodigy.n et... Gordon Youd wrote: Sorry about the sloppy way I put the problem to the group, I'll start afresh.
I have only one main table of "Bookings". Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate and Outdate, which will be called by wildcard date input.
Bob's not the only one confused by this. I'll take another guess at what you are trying to do.
You have one date that you input as a parameter. You are calling that a "wildcard" date. You want to generate a report with a list of customers whose Indate is equal to that date on the left side of the report. On the same report, you want another list of customers, on the right side, whose Outdate is equal to that same date. Hence, you could potentially have customers who appear on both sides of the report.
Is this anywhere close to correct?
-- Randy Harris tech at promail dot com I'm pretty sure I know everything that I can remember.
Now since this is a lot clearer you can do this with one query
SELECT * FROM Bookings
WHERE (Indate = [input the date]) OR (OutDate = [input the date]);
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk... Hi, Bob & Randy,
I want to find from the "Booking" table all "Customers" whose "Indate" matches the "wildcard" input and all "Customers" whose "Outdate" matches the same "Wildcard.
No "Customer" would ever have the same "Indate" and "Outdate".
I would have a list of customers coming "IN" and a list of customers going "OUT on the same date.
I can then deal with both customers on the same day. The Report would show the "INs" in the left hand column, the "OUTs" in the right hand column.
Does this help?
Regards, Gordon. ----------------------------------------------------------------------
"Randy Harris" <pl****@send.no.spam> wrote in message news:X7******************@newssvr25.news.prodigy.n et... Gordon Youd wrote: Sorry about the sloppy way I put the problem to the group, I'll start afresh.
I have only one main table of "Bookings". Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate and Outdate, which will be called by wildcard date input.
Bob's not the only one confused by this. I'll take another guess at what you are trying to do.
You have one date that you input as a parameter. You are calling that a "wildcard" date. You want to generate a report with a list of customers whose Indate is equal to that date on the left side of the report. On the same report, you want another list of customers, on the right side, whose Outdate is equal to that same date. Hence, you could potentially have customers who appear on both sides of the report.
Is this anywhere close to correct?
-- Randy Harris tech at promail dot com I'm pretty sure I know everything that I can remember.
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in
news:e1*******************@news.demon.co.uk: Hi, Bob & Randy,
I want to find from the "Booking" table all "Customers" whose "Indate" matches the "wildcard" input and all "Customers" whose "Outdate" matches the same "Wildcard.
No "Customer" would ever have the same "Indate" and "Outdate".
I would have a list of customers coming "IN" and a list of customers going "OUT on the same date.
I can then deal with both customers on the same day. The Report would show the "INs" in the left hand column, the "OUTs" in the right hand column.
Does this help?
the third version of the SQL I posted will work. You, just need
to add some grouping on the report to get the column to advance
columms on the switch of date field. the easiest way is to add a
boolean expression to the query,
SELECT *, iif([input the date] = indate, 1, 2) AS sortkey
FROM Bookings
WHERE [input the date] = indate
OR [input the date] = outdate
ORDER BY iif([input the date] = indate, 1, 2); .
Go to the Page Setup menu item, make the detail section of the
report 1/2 the width of the header, set the order to "Down, then
Across"
Open the Sorting and Grouping dialog, group on sortkey. set a
header for the group,
Set the group header property New Row or Column to "before" and
voila, its done.
--
Bob Quintal
PA is y I've altered my email address.
Many thanks to all the help, I can now untie the rope from the rafters and
put away the stool ;-).
Kind regards, Gordon.
---------------------------------------------------------
"Jeff Smith" <No****@Not.This.Address> wrote in message
news:e1**********@lust.ihug.co.nz... Now since this is a lot clearer you can do this with one query
SELECT * FROM Bookings WHERE (Indate = [input the date]) OR (OutDate = [input the date]);
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in message news:e1*******************@news.demon.co.uk... Hi, Bob & Randy,
I want to find from the "Booking" table all "Customers" whose "Indate" matches the "wildcard" input and all "Customers" whose "Outdate" matches the same "Wildcard.
No "Customer" would ever have the same "Indate" and "Outdate".
I would have a list of customers coming "IN" and a list of customers going "OUT on the same date.
I can then deal with both customers on the same day. The Report would show the "INs" in the left hand column, the "OUTs" in the right hand column.
Does this help?
Regards, Gordon. ----------------------------------------------------------------------
"Randy Harris" <pl****@send.no.spam> wrote in message news:X7******************@newssvr25.news.prodigy.n et... Gordon Youd wrote: Sorry about the sloppy way I put the problem to the group, I'll start afresh.
I have only one main table of "Bookings". Each booking has an ID, CustomerID, Customername, Indate, Outdate.
From the "Bookings" table I want to extract all Customers matching Indate and Outdate, which will be called by wildcard date input.
Bob's not the only one confused by this. I'll take another guess at what you are trying to do.
You have one date that you input as a parameter. You are calling that a "wildcard" date. You want to generate a report with a list of customers whose Indate is equal to that date on the left side of the report. On the same report, you want another list of customers, on the right side, whose Outdate is equal to that same date. Hence, you could potentially have customers who appear on both sides of the report.
Is this anywhere close to correct?
-- Randy Harris tech at promail dot com I'm pretty sure I know everything that I can remember.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: tyousaf |
last post by:
Hi
i am new to mysql and mysql++, i have installed mysql server, it is
running fine. i also installed "mysql++-1.7.9gcc3.2-2.i386.rpm" (i
have gcc 3.3) , first of all as the readme file says to do...
|
by: techquest |
last post by:
Ok, the query returns,max and min results like:
A,A1,A21,math100,88
A,A1,A21,math104,45
A,A1,A21,Chm100,86
A,A1,A21,chm102,44
A,A1,A22,math101,99
A,A1,A22,math100,86
so on...
which is ok....
|
by: D Denholm |
last post by:
I am a Access newbie... Hopefully somebody can help me figure this
out.
I have a database that looks like:
Asset Economic Minimum
----- ----------------
10555 ...
|
by: trinity |
last post by:
I have two columns:
Plantdate Totalpower
9/26/02 6217
10/31/02 5187
11/28/02 4246
12/26/02 4132
1/23/03 4313
2/21/03 4416
3/21/03 4355
|
by: BerkshireGuy |
last post by:
I have the following data
Month/Year Rep Avg Courtesy Rating Number Returned
--------------------------------------------------------
May 2005 BJM 3.6 2
June 2005 ...
|
by: Andy |
last post by:
Hi,
I've been using this newsgroup heaps recently but after vigorous
searching I can't find quite what I'm looking for:
I have a DB where there are employees who have their skills reviewed.
...
|
by: glen.riddell |
last post by:
I have 2 queries, for the sake of this post I will refer to them as
query1 and query2. Basically query1 returns a number of results from
tables which are grouped and then query2 queries query1 and...
|
by: M26 |
last post by:
I get this message while i'm trying to run Query1, then after I click
ok, my previously saves Query1 is deleted. . . .. any ideas?
|
by: yfangl09 |
last post by:
I have one query with a list of people and required courses they have
to take and another with the same people and courses they have already
taken. How do I generate a query with required courses...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |