Combining Select Query And Count Query

Jul 26, 2006

Ok, hope you can help me with this one.

I have a select query that pulls data from one table that includes the following data

MOS Grade Required Authorized



I have a separate count query that pulls data from another table and counts the following data

MOS Grade Assigned

How do I combine these 2 queries so the results I get will be

MOS Grade Required Authorized Assigned


For instance:

Select Query Says

MOS Grade Required Assigned
25C E4 1 1
42A O3 1 1
38A E7 3 3


Count Query Says

MOS Grade Assigned
25C E4 1
42A O3 1
38A E7 2


I want the results to read

MOS Grade Required Authorized Assigned
25C E4 1 1 1
42A O3 1 1 1
38A E7 3 3 2


Hope you can help.

View Replies


ADVERTISEMENT

Count Query As Well As Select Query

Nov 30, 2007

Hello All,

I'm currently developing a football team website and I'm created a player profile bit of the website linking to my Access Database.

Currently I have the following :

selectCommand="SELECT [playername], [favouriteposition], [picture] FROM [player] WHERE [playername] ='Smith J'

This means I can display John Smiths Name, Fav Position and his Picture from my player table.

I also want to count his stats (goals, starts, subs) but am not sure how to do this.

I have a table called playermatchevents which records matchID, playerID, shirtnumber, goals, starts, subs, mom

goals is a straightforward Number datatype field, whereas starts,subs and mom are a Yes/No datatype field.

Basically I want to keep my select query, but I also want to add to it that it COUNTS the number of starts, subs, goals and mom that that player has.

Pardon me if I haven't used the correct wording, I'm still quite new to all of this.

I would be very grateful for any help.

Thanks,

covcitydv

View 6 Replies View Related

Queries :: Select Count And Group Query

Sep 19, 2014

I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".

Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.

only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.

View 14 Replies View Related

Queries :: Select Query - Count How Many Records Not In Correct Format

May 7, 2013

So I have a table with around 20,000 records and there is a field which holds a phone number for each individual within the organization, I want to run a select query that counts how many are not in the correct format

The format is 10 digits and it must start with a 0

Using Access 2010

View 2 Replies View Related

Difficulty Combining Insert Into Or Select Into With A Union, Please Help!

Jan 23, 2007

I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:

INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")

It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!

View 1 Replies View Related

Combining Two Query's

Jul 13, 2007

table : match
fields : season, matchday, clubA, clubB, goalsA, goalsB, points

I'v filled the table with matches. And also calculated how many points every match has. (1 for draw, 3 for a win). So I have 306 matches, for a league with 18 clubs.

Now I want to make a query that makes a ranking based on points.

I have made two queries.
1. PARAMETERS Waarde Value;
SELECT DISTINCT match.[Club A]
FROM match
WHERE (((match.season)=[Waarde]));

Which lists the 18 clubs distinctively in the season.

2. PARAMETERS Waarde Value, Club Text ( 255 );
SELECT Sum(match.points) AS SumOfPoints
FROM (Clubs AS CA INNER JOIN match ON CA.ID = match.[Club A]) INNER JOIN Clubs AS CB ON match.[Club B] = CB.ID
WHERE (((match.[goalsA])>[GoalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])>[goalsA]) AND ((CB.[Club naam])=[Club])) OR (((match.[goalsA])=[goalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])=[goalsA]) AND ((CB.[Club naam])=[Club]));

which gives me the total points of a club in a league if I input the season with the value Waarde and the Club with the value text.

How do I combine these two. For every resultrow of (1) I want the result of (2) presented next to it.

I'v tried these two queries and they both seem to work okay.. but how do I combine them ?

View 1 Replies View Related

Combining Tables In A Query

Sep 27, 2006

I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. Basically I need a list that has 5800 accounts, shows who has a phone number and who does not have a phone number. I have made a relationship between the two tables based on the person’s name. I used the code below and put the code in the “criteria” and “name” block. However, I received no results. When I remove the code from the query I receive only the results from list 2 (2300 accounts) and no data from list 1. Any Ideas?

FROM [5480 Master Data] INNER JOIN [5480 Meal Collection] ON [5480 Master Data].name=[5480 Meal Collection].name

View 3 Replies View Related

Combining Two Query Results

Jan 31, 2008

I am really struggling with this, but I need to combine these two queries.
They count the number of goals scored and conceded in a fixture between two teams to give the games played, games won lost and drawn and Goals for and against and points tally.

However, I can only get it to work for the Hometeam.
The second query gives the results for the Away team, but I need to combine the two together to give one league table with all the results and points etc combined.

Hope you can help :o

This displays the home team results and calculates everything correctly.


SELECT tbl_Fixtures.TEAMID, TBL_TEAMS.NAME,
COUNT(*) AS GAMESPLAYED, SUM(IIf((HOMEGOALS>AWAYGOALS),1,0)) AS GAMEWON,
SUM(IIf((HOMEGOALS=AWAYGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((HOMEGOALS<AWAYGOALS),1,0)) AS GAMELOST,
SUM(HOMEGOALS) AS GOALSFOR, SUM(AWAYGOALS) AS GOALAGAINST,
SUM((IIf((HOMEGOALS>AWAYGOALS),3,0))+(IIf((HOMEGOALS=AWAYGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.TEAMID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.TeamID, TBL_TEAMS.name;


This displays the Away team results and calculates everything correctly.


SELECT tbl_Fixtures.opponentID, TBL_TEAMS.NAME,
COUNT(*) AS GAMESPLAYED,
SUM(IIf((AWAYGOALS>HOMEGOALS),1,0)) AS GAMEWON,
SUM(IIf((AWAYGOALS=HOMEGOALS),1,0)) AS GAMEDRAWN,
SUM(IIf((AWAYGOALS<HOMEGOALS),1,0)) AS GAMELOST,
SUM(AWAYGOALS) AS GOALSFOR, SUM(HOMEGOALS) AS GOALAGAINST,
SUM((IIf((AWAYGOALS>HOMEGOALS),3,0))+(IIf((AWAYGOALS=HOMEGOALS),1,0))) AS POINTS
FROM tbl_Fixtures, TBL_TEAMS
WHERE tbl_Fixtures.opponentID=TBL_TEAMS.TEAMID
GROUP BY tbl_Fixtures.opponentID, TBL_TEAMS.name;



Now I just have to figure out how to combine the two, which is proving more difficult than I thought it would be.
My SQL isn't the best :o

View 3 Replies View Related

Need A Query Combining Non-used ID Numbers And The MAX + 1

Oct 10, 2005

I'm trying to create a combo box that would show the next ID number available to name a piece of equipment, as well as any numbers in the range that can be recycled.

This is in an equipment database where the equipment is named using the following convention: Division - equipment type - #. For example, a PC would be: DIV1-CPU-0001. My current drop down box generates the next number (so you can see the next ID for DIV1-CPU-0002). It's just a MAX+1 query.

However, if we have 10 PCs and DIV1-CPU-0008 was stripped and sent to recycling, then 8 is available as an ID number. I want my drop-down box to show, in that case, "8" and "11" as the options.

I've created a table with all the numbers and created queries that show which ID numbers aren't in use for each type of equipment. However, I can't figure out how to put all this in one query for my combo box.

Thanks!

View 2 Replies View Related

Combining Info In Tables For A Query

Sep 25, 2005

Hi there,

I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance

1:
country_1 country_2
12
13
14
15
24
25
26
210
35
45
57
512
612
89
1011
1314

2:
idname capital_id
1Belgium 1
2France 4
3Netherlands 9
4Luxembourg 14
5Germany 6
6Italy 12
7Denmark 15
8United Kingdom 11
9Ireland 16
10Spain 17
11Portugal 18
12Austria 19
13Sweden 20
14Finland 21
15Greece 22

3:
idname peoplecountry_id
1Brussel 1370001
2Antwerpen 4970001
3Gent 2357001
4Parijs 21762432
5Lyon 4184762
6Berlijn 34000005
7Bonn 2930005
8Munchen 12670005
9Amsterdam 10380003
10Rotterdam 5760003
11Londen 66780008
12Rome 28260006
13Milaan 15150006
14Luxemburg 790004
15Kopenhagen 4820007
16Dublin 5260009
17Madrid 320000010
18Lissabon 80700011
19Wenen 151200012
20Stockholm 65100013
21Helsinki 48400014
22Athene 100000015

View 3 Replies View Related

Combining 2 Different Tables To Create A Query

Sep 26, 2006

I am rather new to access and only have a public education, so be gentle. My problem is I have made two tables within access. The first one is names and other information (5800 accounts). The second one is names and phone numbers (2300 accounts). The names are in the system exactly the same for both tables. All the names on the second list are contained within the first list. I need to create a table or query that will match the names with the phone numbers and also show me the names that I do not have the phone numbers for. I would really like to have one massive table that contains all the information from both lists. I have made a relationship between the two tables based on the person’s name. Any ideas? Thanks.

View 4 Replies View Related

Combining Multiple Query Results

Dec 4, 2006

Hi,

I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.

I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.

For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...

So at the moment i have two queries per responsibility, and one pulling those two together i.e.:

New Work Items:
SELECT Count(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));

Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;

Is there anyway of combining the first two queries into one, rather than have 3??

Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....

Many Thanks!
Gareth

PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...

View 2 Replies View Related

Combining Data From The Same Column In A Query

Feb 10, 2008

I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.

Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder

The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).

I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).

(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)

SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name

I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.

SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));

Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...

View 2 Replies View Related

Combining Fields In A Search Query

Jan 5, 2005

Hi,

I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:

Strd
12 Rock View
Str
Marston Cresent

change to :

12 Rock View, Marston Cresent

I can add fields together with an expression but cannot divide them by commas, any help appreciated.

M-.

View 6 Replies View Related

Queries :: Combining SQL Statements In A Query

Sep 16, 2014

I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.

The SQL statements are;

FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));

[code]...

View 7 Replies View Related

Queries :: Combining Fields In A Query

Jan 21, 2014

I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?

View 4 Replies View Related

Queries :: Combining Tables In A Query

Jul 9, 2014

I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.

the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.

i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.

View 4 Replies View Related

Queries :: Combining Query Results

Nov 5, 2014

I have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.

View 4 Replies View Related

Queries :: Combining Query Not Showing All Results?

Jan 29, 2015

Sales will make calls and record data using a form (frmEnquirySpeak) which will save to tblSpeak. If the call results in a meeting being booked then it also updates tblMeeting with a date of the meeting (simple stuff so far!).

A sales person might record up to 14 types of action per day, qrySpeakPerAction counts the different types of call made on a day by day basis for reporting. qrySpeakGroupedAction summarises this down to 8 categories and applies a date filter selected on a reporting form (and this works well).

I now want to include the qryMeetingsBookedPerDay (counts m_date_raised and groups by m_date_raised...eg 2 on 24/12/14, 4 on 28/12/14 etc etc) with the qrySpeakGroupedAction...

My effort so far has resulted in only dates which appear on BOTH queries showing. This may not always be the case.
Every day calls will be made, this may or may not result in a meeting. So I see there being 2 options:

1 - Query to show the count of calls on a date or date range and number of meetings booked each day that calls are made (prefer this method, it sounds simpler)
2 - qryMeetingsBookedPerDay should auto populate days which dont appear as 0 (this sounds messy and not sure what capabilities/speed are like when managers want to spot patterns/trends over multiple years?)

View 7 Replies View Related

Queries :: Combining Like Records In Union Query

Nov 25, 2013

How can I add the resulting records from a union query. The results of the union are something like this:

Quantity Item

2 Cats
3 Cats
1 Dog
4 Mice

What I need to display is

Quantity Item

5 Cats
1 Dog
4 Mice

How can I get the query to "do the math"?

View 3 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Queries :: Combining Data In Access Query Using Builder Function

May 13, 2014

I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:

NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed

I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:

NameColour
CarBlue;Green;Yellow

How i would do this.

View 5 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

Queries :: Drop Down - Query Not Combining And Filtering Values Based On A Form

Jul 22, 2014

I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.

The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.

I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").

Using Windows 7,
Access 2010

Is there a way to select multiple values from the drop downs?

View 14 Replies View Related

Union Query - Generating From A Select Query ?

Jun 23, 2006

(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-

Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)

Review Tables

Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)

The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.

The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)

I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;

SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;

I can then append the info into a new table. However this query is run at least twice a day and things change.

I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?

Any help greatly appreciated.

View 2 Replies View Related

Bug With Wrapper Select Query On A Union Query

Sep 14, 2007

I have the following query:

SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM (

SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1

UNION

SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper.
Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM [

SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1

UNION

SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable
Now when you want to execute the query you get an errormessage.
Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved