Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

How To Create A Cross Tab Query With SQL Statements

I am not that knowledgeable with Cross Tab queries, but I have a couple that I created in Access. I am now writing a VB program and want to know how I could write an equivalent cross tab query SQL, in my VB program and insert this into a table. Here is my query in Access:

TRANSFORM IIf(IsNull(First(tblMasterMerStsts.Count)),"-",First(tblMasterMerStsts.Count))
SELECT tblMasterMerStsts.Report_ID
FROM tblMasterMerStsts
GROUP BY tblMasterMerStsts.Report_ID
PIVOT tblMasterMerStsts.Date;

But I do not think you can just put this in as a SQL statement in VB.

Is there a way?
Thank you

Edited by - itmasterw on 2/11/2005 10:14:25 AM

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Does any one know how to write a CROSS TAB query in SQL? Here is sample data i have in my table and the out put i am looking for.

My Table Data:
1ABUCB19 04-Apr-06
1ABUCB21 27-Mar-06
1ABUCB39 27-Mar-06
1AMACM12 27-Mar-06
1AMACM22 17-Jun-06
1APACP22 17-Mar-06
1APACP13 11-Mar-06
1DMACM12 27-May-06
1DMACM22 13-May-06

I need the out put like this,
1A B19 04/4/6 M12 27/3/06 P22 17.3/06
B21 27/3/06 M22 17/6/06 P13 11/3/06
B39 27/3/06
1D M12 27/5/06
M22 13/5/06

Update A Cross Query
Heya guys,

This maybe a simple question but here it comes ;-)

Is it possible to add and edit data via a crossquery ?

Tnx in advance !

Grtz Noodles

Cross Tab Query Sql Server
Any One Here Familiar with cross tab query i need some examples on cross tab quries.
i am using sql server 2005 express edition

i ve a following tables

1St Table Of Classification
id Classification
1 Brand
2 Model

Second Table Class Description
id ClassId Class Description
1 1 Toyota
2 1 Honda
3 1 Suzuki
4 2 EE100
5 2 EE80
6 2 FF

Third Table Of Item
Item id Class Description Code
1 1
1 4
2 2
2 6
3 1
3 5

Result Required
Item Id Brand Model
1 Toyota EE100
2 Honda FF
3 Toyota EE80

Any ideas I ve Created A stored Procedure as it very lengthy so i ve treid to make my self clear by giving this example.
the stored procedure i ve made seems a little unrelaible as Some time problems occur.
so any one here used a cross tab query on large scale ??

Cross Database Query
is there are a way that I can query cross databses. I mean sybase database but it has different schema,


select A, B, C from test1..tableA employee, test2..tableB user where emplyee.ID = user.empID

test1 and test2 are different database in the same server and same it is sybase database

can this be done. Please help me I am newbie

I'm moving an application from A2k to VB6.  I have a cross tab query that I've made.  Then I made a make table query using the cross tab as my source in access.  Moving to VB, how do I make a table from the cross tab results?  Do I actuall have to open a record set containing the cross tab results and manually put that into a table or is there a SQL statement some how that will combine the cross tab and the make table into one query?  


Varchar SUM / Cross Tab Type Query In SQL (ms)
I have 2 tables:
Table 1
IDf |Desc
1 |One
2 |Two
3 |Three
4 |Four

Table 2
IDa |IDf
1 |1
1 |2
2 |2
2 |2

What I need is some way of presenting the data like this:
SUM(IDa) as NoCols, <A Function>(Desc) as AllDesc From <etc> Where IDa = 1


NoCols |AllDesc
2 |One,Two

Can this be done? Thanks in advance!

Help With Cross Database Update Query?
I have a small (400ish records) local table which drives a local billing process. VB code drives the billing on a monthly basis.

Another (massive) remote database stores updated address etc information.

I'm trying to use VB (and ADO) to perform an update query during the billing process which will update the local table with address etc information from the massive table.

I am not sure exactly how to go about this. Currently I am thinking I need to get a recordset from the remote table and then use this in an update query to the local table. I was hoping to find a way to restrict the recordset coming back from the remote table to only return records that exist in the local table. I am not sure how to do this, as the key identifier for the two tables is non-linear and has no logical pattern to distinguish it from other records in the remote table.

Is there any way I can use a JOIN across two different databases? And is there a way to go about performing an update using the recordset object without iterating through each record via a loop?


Complicated Query With Expressions,sum,cross Tables..

I have to make a query with the following information, that is stored in 3 datacombo's:

1. maatschappij.naam (for every name, there is a specific BVVO number)



the query i tried to make looks like this:

Sum(boekingintern.debet) AS debetint,
Sum( AS creditint,
Sum(Claim_definitief.debet) AS debetext,
Format(boekingintern.datum,'mm') AS maandextern,
Format(Claim_definitief,'mm') AS maandintern,
((Agentennummer INNER JOIN boekingintern ON
Agentennummer.agentnrintern = boekingintern.agentnrintern)
INNER JOIN Claim_definitief
ON Agentennummer.agentnrextern = Claim_definitief.agentnrextern) ON
Maatschappij.BVVO = Claim_definitief.BVVO) ON
Agent.BVVO = Maatschappij.BVVO) INNER JOIN Polisnummer ON
(Claim_definitief.polisnrextern = Polisnummer.polisnrextern)
AND (boekingintern.polisnrintern = Polisnummer.polisnrintern)
GROUP BY Agentennummer.agentnrintern,
HAVING ((("agentnrintern")="00000004")
AND ((Format([Claim_definitief].[datum],'mm'))=12)
AND (("naam")="Ethias"));
the information after the having -clause is taken out of those 3 datacombo's, as i mentioned above..
I already made this query in design view, but their is another problem now:
i need to insert this somewere:
format(claim_definitief.datum,'mm') = format(boekintern.datum,'mm')
because i need the query to get me records from the same month. (the exact date is never the same, only months will match..)

the tables look like this:
agentnrintern agentnrextern naam
001 - 5555ag01 - wim
002 - 1234ag01 - michael
003 - 3333ag01 - koen
004 - 5555ag02 - goffin
005 - 1234ag02 - vanmol

BVVO [u]datum polisnrintern agentnrintern debet credit
5555 - 23/07/2004 - 1KJHLKJ22L - 2LKM2222 - 0 - 123
5555 - 4/04/2004 - AJ11LOO1 - LKJLAA99 - 19220 - 0

BVVO agentnrextern datum polisnrextern debet credit status
5555 - 5555ag01 - 12/10/2004 - 1213DFSDF - 23 - 0 - 0
1234 - 1234ag01 - 22/11/2004 - 2KM2MM22 - 120 - 0 - 0
1234 - 1234ag02 - 23/11/2004 - 3213fgdfg - 0 - 125 - 0

BVVO naam paswoord procentueel absoluut
1234 - KBC - Michael - 20 - 1000
3333 - Fidea- Koen - 4 - 28
4444 - DVV - 4444 - 3 - 25
5555 - Ethias- Wim - 4 - 30
6666 - Fortis - 6666 - 2 - 15

polisnrintern polisnrextern
pn1 - 3213fgdfgdfg12312
pn10 - UHKIUYH267987
pn11 - 15kuoiuoi456
pn12 - 1213DFSDF1S3DF1
pn15 - qsdf132
pn18 - qsdf153

until now, there is an error message, saying that 'agentnrintern' is not part of a static relation'

when i run this query in acces, the result is null..

Can somebody help me with this?

How To Create A Cross - Tab Report In Data Reports
hi buddies

pls help me in creating a cross tab report using
data reports with VB 6.0

my requirement i have 3 cloumns in my table
1) emp code
2) month code
3) Amount for corresponding month
i want the report as ,.. which looks like
emp code | month code1|month code2| month code3....12

amount amount ........
thanks for helping


S.M. Syed Sherfudeen
Scientific/Technical Assistant-'B' ,
NIC, Madhya Pradesh State Unit,
Computer Centre 'C' Wing Basement
Vindhyachal Bhavan,
Bhopal - 460 004,
Madhya Pradesh.
Ph. 0755-551265 ext.112

SOLVED:Question About Setting Up A Cross Reference Query In MS Access
First off I'm very new to Access. I've had some help from a friend that programs databases as his job so he told me that I should set up my database with as little overlap of data in each table.

My question is how do you design a cross reference query. My data structure is two tables. In table one (All_Player), I have all the information about the people that are going to be on the teams. I have set the primary key to be autonumber so I can have people with the same in the data. In my second table I have (Teams), where I want to put the autonumber value from the All_Player table in each of the available positions. So far that has not been a huge problem but I would like to run a query that would allow me to Print the team data and replace the autonumber value with the Players Name. I'm a bit lost so any help would be greatly appreciated.



Edited by - Maxizarius on 11/1/2004 10:59:24 AM

Please Help Me Create Monthly Statements.
Hey Everyone
I have a program that creates Invoices for people who have not paid. And I have a Table called Transactions with such fields:


So to retrive the Debtor List I just run a query like this:


Select * from Transactions where Stautus='Unpaid'

And it works all what I wish to do is enhance this feature a bit by giving users an option so that they are able to mail the statement at month end. Right now what I have for that is:


Select * from Transactions where GroupID=77 And Status='Unpaid'

I ask users to enter a Group ID for the above query.
With the query mentioned above it returns all the unpaid transactions and I put them in a MS word tempelate and works all good. So how can I give users a feature that at month, the statements get created automatically so that users dont have to go thru all the records and entering the GroupIDs. Your help is much appreciated. Please gimme some ideas.

Activex To Create SQL Statements
Dear all,

Is there any ActiveX available to create SQL statements (Select qry is enough).
I want to wrap this as tool in my system


Embedded Query In Sql Statements In Code
How can one embed 1 query in another using SQL statements in VB6. In access the query needs to be split or erors are generated. I don't want to use stored procedures; I want to use the SQL string in the ADO command.

Is there some way to use the recordset created in step 1 as input in step 2.



Program For Cross Database..cross Platform
Hi Folks,

I would like to know how to develop a program in Visual Basic which as access cross database at runtime. this program has a user interface..where user selects the database he oracle, sql server, sybase..etc..then he selects database..then tables..then fields...
all these information will be decided at runtime..





How To Create Flexible SQL Statements For Reports?

as of now, the SQL statements that produce my reports are static. is there any way to the user of my program to enter a parameter, that will in turn be included in the SQL statement, and produce the required report?
the scenario: the user will input the "expiry date":, and a report will be produced listing memberships that expire by that date. how do i make the "expiry date" be included in the SQL (my report now lists all members).
also, i have 2 radio-boxes: sort by membership number or name. when the user selects one option, it will also go into the SQL (in the SORT statement). like the above scenario, how do i include this too?

VB To Generate Create Table Statements From Erwin
Has anyone used VB to generate DDL using the API for Erwin 4.1 ?(modeling tool)
I have used the internal Erwin macros to generate DDL, but it is very manual intensive, and I would like to use the API and VB to generate the DDL. Any ideas or sample code?

ADO Howto Execute TSQL Statements Like Query Analyser
How can I execute a TSQL statment using ADO like QueryAnlayser does?

For example the following SQL statement returns a recordset in Query Analyser, but not if you use it as the commandtext in an ADO command object:


CREATE TABLE #authors(au_id varchar(11) PRIMARY KEY)
SELECT au_id FROM authors
OPEN cur1
DECLARE @au_id varchar(11)


-- Fetch the nth row in the cursor.

INTO @au_id

    INSERT INTO #authors VALUES(@au_id)


    INTO @au_id

    PRINT Cast(@COUNTER as varchar(8))

CLOSE cur1

SELECT * FROM #authors
DROP TABLE #authors

SQL Syntax: IF Statements To Create Dynamic WHERE Clause [99% Resolved]
The problem I'm having is that I have several comboboxes and a textbox that I need to use to filter the query results. To keep it short, the user could specify any or all of these fields:

Part Number     
Sales Rep     

Pretend my query is simple:


Now if I pass each criteria as a parameter (just stick an @ in front of above list), how can I check if the value isn't blank, and include it in my WHERE clause? Division will always have a value.



WHERE Division=@Division

If Len(@Facility) Then "AND Facility=@Facility"
If Len(@PartNumber) Then "AND Part_ID LIKE '%@PartNumber%'"
If Len(SalesRep) Then "AND SalesRep=@SalesRep"


I've done some block if's, but I'm not sure how to generate this.

Dave Applegate
Microsoft VB MVP, ACE [FAQs]
My friend has a trophy wife, but apparently she wasn't in first place.

Edited by - Metallisoft on 11/14/2005 9:33:40 AM

Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?

Sub Collect_HealthPlan()

Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command

'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection

'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"

'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")

'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize

'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount

i = 1

'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
    If ctrlHPlstbox.Selected(CurrentRow) Then
        HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
        'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
        HealthPlan_Selected = i
        i = i + 1
    End If
Next CurrentRow

i = 1

'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
    QueryBuilder(i) = SQLString
    QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
    If ctrlNBchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
        Next CurrentRow
    End If
    If ctrlNBEAchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
        Next CurrentRow
    End If
Next i
End Sub

Let me know if you have a better way to do this..Thanks for all your help!!!


Edited by - bahamaej on 3/3/2005 4:50:53 AM

Help Needed To Create Such An SQL Query, A Looong Query?
I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

I have three tables with the following data:
Table one: tblAssessment

UserID companyName SportName CountryName
   1 2        6         
   2 4        8    
   3 3        7
Table two: tblDropDown

DropDownID ParentID Name
1 0 CompanyName (as CompanyName has DropDownID=1, all the companynames will have parentId 1
2 1 Nike
3 1 Reebox
4 1 Puma
5 0 SportName (as SportName has DropDownID=5, all the Sportnames will have parentId 5)
6     5 Football
7     5 Baseball
8 5 Rugby

Table three: tblSummary

CompanyName SportName CountryName
Nike     Football England
Reebok     Baseball USA
Puma     Rugby NewZealand

Below is my brief explanation of the tables and fields

tblAssessment have 4 Fields:
UserID (PrimaryKey)
CompanyName(this stores an ID that refers to the name in the dropdown table)
SportName(This stores an ID that refers to the sportsname in the dropdown table)

tblDropDown has three fields:
DropDownID (Primary Key)

The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

tblSummary has three fields:
This table has pure text data for all fields.

I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

Could someone plz guide me how to create such a query?

I have attached a sample access database that mirrors the same above format.


I Need Help To Create My Query.
Greetings! I really need help to create an SQL query cuz I really am having troubles figuring this one out.

Here are my tables:

tblItem tblContents tblProperties
=========== =========== =============
*pkIdItem *fkIdItem *pkIdProperty
sDescrItem *fkIdProperty sDescrProp
The * represents the primary keys.

The relations are as follow:
tblContents.fkIdItem is a foreign key of tblItem.pkIdItem
tblContents.fkIdProperty is a foreign key of tblProperties.pkIdProperty


Contents of tblItem:

pkIdItem sDescrItem
-------- ----------
1 Radio
2 Phone
3 Toolbox

Contents of tblProperties:

pkIdProperty sDescrProp
-------- ----------
1 Shape
2 Height
3 Weight

Contents of tblContents:

fkIdItem fkIdProperty sDescrProp
-------- ------------ ----------
1 1 Other
1 2 20
1 3 30
2 1 Round
2 2 20
2 3 45
3 1 Square
3 2 20
3 3 30
Now, what I want to do is to get a list of Item that have have a Height of 20 AND a weight of 30.

I know it really looks simple, but I just can't seems to figure out how to build the query!

Thank you for your help, it really is appreciated!

How To Create This Query?

I need to create a Query to filter out the product sold and how many were sold

here the table the information is stored in

So the user would ask for a report on the product code 5
the report would show the amount sold of this product on each order and then give a total on how many sold

I'm new to access and have no idea how to do this sort of query.

Please don't use sticks on me to beat it in to me.

Create Query In Vb

Is it possible to actually create a query in VB and then send the contents to excel.I am think of of having a list box to display the fields of my Access table.The user then chooses which fields they want and then when they hit submit, it sends the results to excel.

so if they selected ticket no, date open, date closed etc they could produce a report in excel where they could modify further.

Any help, ideas would be much appreciated.



Create A New Query

i have an access application referenced by appRef

is there a command to create a query in appRef?

Need Help To Create A Sql- Query
need help to create a sql- query ...?

Create New Query
I want to create new Query in access from VB

the query code i want is:

SELECT [TABLENAME].[Date], [TABLENAME].[Strike], Sum([TABLENAME].[Call]) AS Call, Sum([TABLENAME].[Put]) AS Put, Sum([TABLENAME].[Net]) AS Net

but i dont know how to creat new one
plz help me

Help Me To Create SQL Query
Here's the database in Access.

I need to delete one Group (I know it's key) and all the Persons related to this group.
Both relations set to Enforce Referential Integrity but without Cascade Update/Delete.

How can I do this?

Create A New Query In Access Using ADO
Hi peeps,

Hope you guys can help, I have searched the forums but cannot find anything similar to what I need, so here goes...

I wish to create a new query in an Access table using VB6, I have established a connection but am unsure what commands to use to create a new query, I wish to create the query with the following SQL:

SELECT [Order Lines].Date, [Order Lines].Time, [Team Members].Name, [Order Lines].SalesPrice, [Order Lines].Qty, Category.Description, Service.Description
FROM ((([Order Header] INNER JOIN [Order Lines] ON [Order Header].RefNo = [Order Lines].RefNo) INNER JOIN [Team Members] ON [Order Header].PrimaryTeamMember = [Team Members].ID) INNER JOIN Service ON [Order Lines].ProductCode = Service.Id) INNER JOIN Category ON Service.Category = Category.Id
WHERE ((([Order Lines].Date)=#11/30/2002#));
Actually, while I'm at it, can anyone reccomend a good, comprehensive guide to manipulating Access databases through VB6?

Any help would be much appreciated.

As always, many thanks

Create Table Sql Query
Can anybody give me a sample create table sql query to use with access database. I need to create a table containing some textfields and number fields

Create A Query Table
Anyone who knows how to create a query table in pure codes?

How To Create Query In Access Using Vb6 0
Hi, to create query in access using vb6.0 through ADO's.
without open mdb file how to create query

2. how to display access report through vb 6.0.

anybody help me.

thanks in advance

Create SQL Query For VB Datareports
Question:Please tell me how to Pass Variables to Data reports Eg. "Select * from Tabname where name=" & var

Problem In Create A Right Query
I have two table and i want to select from them the maximum Fdate Field value.
i do it whith following query.but if my Fdate where equal to each other it can not select max(fdate)in order by it's ftime value
what can i do
Code/Serial/Fdate /ftime
1 / 1/05/01/02/08:00
1 / 1/05/01/09/08:00 'Is select
2 / 2/05/01/01/08:00
2 / 2/05/01/02/08:00 'Is select
3 / 5/05/01/10/09:30 'Is select But is wrong
3 / 5/05/01/10/10:00 'Is select But is wrong
3 / 5/05/01/10/10:20 'Is select

select Internal_Code,MidName,serial,NewPieces,UsePieces,Ekhtesar,Max(FDate)as FDate,ReqNo,Comment,stocknameid,id from stockseriallist_view P
group by
having Max(fdate)=(Select Max(fdate) from tbl_stockFunction Q where q.stocknameid=p.stocknameid and

How To Create Query From 2 Mdb Files Using ADO
Please help me
can we create query from 2 mdb files using ado?

Edited by - muchtar on 7/3/2004 7:58:47 AM

Using An Existing Query To Create Another
Hello Everyone !

I'm using SQL commands on an access DB.
My problem is that I need to do a SUM command on a GROUPED BY field. And a GROUP BY command on a SUMMED field (two different fields).
I figured the best way to do this way to create two queries, one to make the first level commands (The first GROUP BY and the first SUM) and another one to apply the second level commands (The second SUM and the second GROUP BY).
But I need to use the data in the first query to make the second one...
Anyone knows how to pass the first query results to another "SELECT" ?

Create New SQL Code For A Query In Access?
I am generating a report based on a query. The query prompts the user for a product type. Here is the current sql for that query:

SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]) AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];
Note the section where is has the prompt:

WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]
Now, in a userform, I would like the ability to select the product type and have that query use the product type from my combobox. How can I (through code) modify the query to reflect the new SQL? Something like:

SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like "Clamps") AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];

Create A Multiple Parameter Query
Hi again. This thread is following the earlier KanBan question. I would like to create a way for users to select records via typing a list into a box. After typing in values that they wanted, the values would be passed on to a report to be printed.

What would be the best way to do this? I know of several problems; those being passing multiple parameters and filtering them to the report. Ideas?

I have already seen some examples of this using a multiple selection list box, but would like to be able to enter these values manually.

How To Create A Stored Query In Access Using Vb
Hi, can anyone help me how to create a stored query in access and run it using vb. Thanks a lot

Create Access Query @ Run-Time ADO
I could use some help on how to redefine a saved query in Access using ADO.
I have a report whos data source is a saved query, is there a way that I can build a SQL statement in VB then save the new SQL to the existing query and run the report? I've read a tutorial that shows how to do this in DAO, but I'd prefer to use ADO or ADOX if possible.
Here is the SQL statement with a hardcode value, I used the query builder in Access to achieve this, the only part that will be a variable is the WHERE statement. The variable will be retrieved from a form with a combobox.


SELECT Cards.Name, Cards.Description, Drawers.Name, Drawers.Description, Racks.Name, Shelfs.Name, Exchange.Name
FROM Exchange INNER JOIN (((Cards INNER JOIN Drawers ON Cards.Drawer_ID = Drawers.Drawer_ID) INNER JOIN Racks ON Cards.Rack_ID = Racks.Rack_ID) INNER JOIN Shelfs ON (Drawers.Shelf_ID = Shelfs.Shelf_ID) AND (Cards.Shelf_ID = Shelfs.Shelf_ID) AND (Racks.Rack_ID = Shelfs.Rack_ID)) ON (Exchange.Exchange_ID = Cards.Exchange_ID) AND (Exchange.Exchange_ID = Shelfs.Exchange_ID) AND (Exchange.Exchange_ID = Racks.Exchange_ID)
WHERE (((Exchange.Exchange_ID)=16))
ORDER BY Shelfs.Bottom_Left_Pos;

If searched the forum and the closest I found was a post for Oracle and ADO. I read it but I was lost.

I'm not looking for all the code just the push in the right direction.

Thanks in advance


Is It Possible To Create A New Query Working In My VB6 Program?
I am making a program for the registration of employees. Al those employees belong to a list of several lists. The data of the employees is in an access-database.
Know when the people are working in their VB6 program they want the possibility to make new lists(=new query's) and add employees to them.
Know my question is: is it possible to create a new query when I am working in my VB6 program? if yes how?

I want a new list called: teachers. I add that new list name to the table of list names trought my VB6-program. Know I want to add people to that list through a query ??how can I do that?

Thank you

Create A SELECT Query At Runtime
HELP!!! This is more a SQL question, but can't find any help anywhere, so I am hoping there is a SQL savvy brain-iac out there who can lend a hand!

I have been trying find a way to create a SELECT query at runtime based on the user selecting values from five combo boxes. They must select at least one, but may select any combination or even all five. The data comes from a hierarchy of tables. My table structure can be one, two or three tables deep depending on what selection criteria they elect and may be comparing values in as many as six tables looking for a match. The SELECTed data always comes from the same two tables and will be the same fields no matter what search criteria they have elected to filter on.

Does anyone have an idea how to go about setting up this query??? I have tried using UNIONs, but the result ends up like an OR query rather than an AND showing any record that matches even one of the criteria.



Create A Table From Select Query
hi friends

I have my database in MS Acess

i want to create a table with select query how can i do that

i know i can do that with _

db.execu(create table tmp(bscode text 12))

but can i create with selecting the fields along with data from a another table

Please help

Create A Recordset From An Access Query
It seems like it should be an easy task but i cant find anything on it!! I have found various articles about ADOrecordets but i dont think that is what i'm looking for. I have a form where the user enters criteria. I wish to run a query and put the returned results in a recordset. I will then use that recordset for populating an excel file (but that is for another day !!!). So the question is if it is possible to create this kind of recordset???

Query In Access To Create Report
Respected Dear Friends

One query in access2000 make me very serious because it becomed headche for me. U can belive but I spent 50 hours after it but I can't. I hope someone fromu can solve it ina second. Please I ma very eager and anxious to know it's solutions. Please help me soon
I m waiting here.

I have two tables in_peeled and paid_payment.

Fields of Table : in_peeled
(1) cat1 ------------> text
(2) cat2 ------------> text
(3) date ------------> date
(4) cardno ------------> number ( It has duplicate values)
(5) in ------------------> number
(6) out ----------------> number
(7) payment ----------> number

Fields of Table : paid_payment
(1) cat1----------------> text
(2) cat2----------------> text
(3) date----------------> date
(4) cardno-------------> number    ( It has duplicate values)
(5) paidamount-------> number
(6) restamt------------> number

From these two tables I required a report in vb6 by cat1,cat2 and datewise.
Parameter like these I enter :
cat1=BIG, cat2=RED and date Between #01/01/2003# and #31/07/2003#

Required fields in report. Parameters: cat1,cat2,date
(1) cardno from in_peeled
(2) Sum of in from in_peeled
(3) Sum of out from in_peeled
(4) Sum of payment from in_peeled
(5) Sum of paidamount from paid_payment
(6) Sum of restamt from paid_payment


Create DataReport In VB Using Query Table
I have one query table which consist of data form 2 table in my database. I want to make a report using this query but the option i found in data environment consist only table. Please anyone has any idea how to make my query to be display in my report?

Help To Create A Criteria??( Sql?/query) For A Report
hello all I have a form that has several buttons to access different criteria to create a report for my vb6 app. there is a datacombo box that you select a name and I want that (specific name) to only show on the report = that name(not all the names) with additional info from this report, such as date of the person and additional info about the person, just with that name. one of the sort needs to be the date that pulls up form the database, since this is supposed to generate from the access database using dataenvironment. I am un sure how to create the code for this, cus i want to pull the report when click on the commandbutton, selecting the name and showing all the rest seperating by the date.... How would i create this??? im not that familar with sql queries?? please help....

VB6 Create Excel And Populate With Query - Stuck!

I am creating a program with VB6 and storing data in Access. I want to create an Excel instance from within VB and then populate it with the all data from my query. I have got so far with this and now very much stuck.
I would be gratefull if somebody could give me a bit of guidance so that I can get my head round this.

Thanks in advance

Private Sub CmdReport_Click()
Dim sSql As String
Dim i As Integer

sconnect = "provider=microsoft.jet.OLEDB.4.0;data source = c:adodb1.mdb"
sSql = "select * from qryfullreport"
Set cn = New Connection
cn.Open sconnect
Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly

Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet

Set oExcel = New Excel.Application
oExcel.Visible = True

Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets("Sheet1")

Create A MS Query From Within Excel And Refresh The Same Programmatically!
Hi Everybody

This is the requirement :-

Using the Data -> Import External Data -> New Database Query functionality in Excel, I have written a query (using MS Query) to extract some information from our our SQL Server based datawarehouse. The data is returned to the Excel spreadsheet and this spreadsheet is saved somewhere on the network.

Rather than extracting data directly from the cubes (I am not familiar with MDX at this stage to the extent to be able to easily extract the same data directly from the cubes), I am using SQL statements to extract data from the tables that sit behind these datawarehouse cubes.

I now have a report template document, which the user uses to generate the monthly report. However, before he/she is able to do update the report every month, this data needs to be refreshed with the latest data. I have a button on my report template document and what I want is that when the user clicks this button, the program should open spreadsheet containing the MS Query, update the data from the data source in the background (based on the user input of the latest period which the data needs to be updated for) and return the data to this spreadsheet. The program then would recreate the associated named ranges to work with the enlarged/altered data range so the formulas continue to work without having to alter them.

Any ideas/suggestions/pointers how do I get to refresh the query and alter the SQL within using VBA?

Best regards

Deepak Agarwal

How Can I Create A Query To Access 2000 DB Thro VB?
Dear experts,

My aim is to run the DDL statemnets for Access 2000 thro VB.
That is I try to code something as follows: (I use ADO connection to the Database)

connectionvariabl.execute ("create table tableName ....")

Its working fine and the table is created.

Likewse I try to create a query as follows;

connectionvariabl.execute ("create view viewName as ....")

when executing , it says "invalid sql syntax".

I try with ADOX.Catalog object to acheive this.

I use something like

'msgbox connx.Views.Count'.

Its displays the no of select queries in my database.

Then i tried msgbox connx.Procedures.Count

It shows the no of action queris in the db.

But I am unable to create a query in my db.

You please tell me any method to create query in Access 2000 db thro VB

Thanks in advance

Copyright 2005-08, All rights reserved