Creating Reports With Multiple Tables
OK, I'm not going to lie....this is my first attempt at trying to create a report using access. How do you link tables into a report?
In my design view I see how you can bound the text fields to a field in the table, but How do I bound to other tables? Do I have to build an expression or query?
Here is what I need to do for example. I need to put the name of the person on the report however, he report I built is from a froeign key table so there is just an ID. How do I get the person's name that has that ID to show up on my report?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Crystal Reports And Multiple Tables
I have a report using Crystal Reports v8.5 and an Access 2000 database.
The report gathers info from several tables and I am trying to create a "Many to One" relationship but the Visual linking Expert doess not give me that option.
The help file refers to and defines this but doesn't tell you how to do it.
Crystal Reports 8.0 & Multiple Tables
I have a report that will utilize around 15 tables that contain child data to a parental table utilizing Access 97.
The report is a Complete Medical Record that will fit on approx. 3 pages when finished. The report for a specific case. The report does not use any calculations, but does list almost all fields for each table in succession.
My question is this:
What would be the preferred method of creating this report when utilizing VB 6.0 as the front end for parameters?
Should I use a TTX file.
Should I create this report using and embedded .ocx control.
Should I create this report using Crystal's Designer which I have had many techinical problems with?
Creating Multiple Tables From One
I'm searching for a method of creating multiple like tables from a single larger table in MS Access based upon the contents of a data element changing. Please help
Creating Data Reports From Join Of Three Tables
please suggest how to create data report where data is bein fetched from join of 3 tables.
As i am creating reports for the first time in , i m facing a lo of trouble...with data environment, i dont know how to mentio data source in terms of " app.path" and hence the defaul c:/desktop/mydatabase.mdb is being accepted as the path by th connection.
Moreover can anyone plz suggest if i cn use PL/SQL 4 data repor creation...as the table/sql statement doesn't serve m purpose...or can i use the stored procedure option to call procedure to display...n if so how??
i am pretty hopeful of ur cooperation n suggestions...
thanking u in advance....
Creating New Rows With Multiple Tables
I am developing a front end for a SQL Database wich has multiple tables. My primary table "Customers" has an ID set to be an auto number a Child Table "Address" has an Identifier Id that should be the same as Customer.ID. When I try to create and Add the New rows to the tables the Address table has an error with the Id field. I am trying to figure out how to set the Address.Id to the Customer.ID that is generated when the row is added to the table. I can't Update the dataset with out having a matching Address Table. Please help
Creating A Recordset Using More Than One Tables From Multiple Database
I have to create a recordset using 3 tables, but the problem is this that some of the tables are listed in more than one databases.More precisely lets the query string is "select * from table1,table2" then in this table1 belong to database db1.mdb and table2 belong to database db2.mdb. Now at a time we can open a recordset using only one connection. And these two databases has 2 connection.
I am using ADO with back-end as MS-ACCESS.
Can you please help?
Thanking you in anticipation.
Tricky Problem With Creating Multiple Tables In Word From VB
Please save my sanity....I began responding to a thread on another forum earlier today and we got pretty far with the problem save for what follows:
Initial objective: Read the rows from multiple ListViews on a VB form, then transfer them into tables into Microsoft Word. There are six or more listviews per form, and each form needs a button that will magically export all to Word (and then email to a list - although this does not regard my question here)....
We have figured out how to get the data from the listview into ONE table, but after the first table, the code crashes saying "Item not in collection". This is because the second time we try to add another table, the apparant index remains the same. I say "Apparanet" as we have VB experience but not with Word - the attached project was pieced together combining a class I downloaded (with modifications) and some of my own library of functions/subs. The following code demonstrates where the error is:
Public Sub InsertTableWithData(m_Recordset As ADODB.Recordset)
On Error GoTo Error_Handler
Dim intNumofRows As Integer
Dim intNumofColumns As Integer
Dim p As Integer, ColWidth As Integer
Dim i As Integer
intNumofColumns = m_Recordset.Fields.Count
intNumofRows = m_Recordset.RecordCount
'Insert a new table with rows according to recordCount plus Column header
'and the number of columns in the recordset
wrdDoc.Tables.Add wrdSelection.Range, NumRows:=intNumofRows + 1, _
NumColumns:=intNumofColumns, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
iTableCounter = iTableCounter + 1
With wrdDoc.Tables(iTableCounter) '<----- this is where error occurs on second calling of this sub in an external loop that calls it
' Set the column widths
For i = 0 To intNumofColumns - 1
ColWidth = Len(m_Recordset.Fields(i).Name)
.Columns(i + 1).SetWidth ColWidth * 25, wdAdjustNone
.Cell(1, i + 1).Range.InsertAfter UCase(m_Recordset.Fields(i).Name)
' Set the shading on the first row to light gray
.Rows(1).Cells.Shading.BackgroundPatternColorIndex = wdGray25
' Bold the first row
.Rows(1).Range.Bold = True
' Center the text in Cell (1,1)
.Cell(1, 1).Range.Paragraphs.Alignment = wdAlignParagraphCenter
' Fill each row of the table with data
For i = 1 To intNumofRows
For p = 1 To intNumofColumns
FillRow i + 1, p, m_Recordset.Fields(p - 1)
p = 1
Set m_Recordset = Nothing
wrdSelection.MoveDown Unit:=wdLine, Count:=intNumofRows + 5 '<----this is attempt to correct but failed - code still fails without it
wrdSelection.TypeParagraph '<--- ditto as above
Debug.Print Err.Description & " - " & Err.Number & " " & iTableCounter
The above routine gets called in a loop...first time is successful, second time it crashes because the index went up, but the second table was added to the first element (apparantly)...after the loop that calls this sub terminates, the file is saved. I open the file and I see a table with a bunch of blank spaces, then beside that, another one that is populated (before it crashed of course), although one column is in the wrong place. We have tried skipping lines, paragraphs, you name it - bottom line - we know little about VBA for Word (but a fair bit about VB!! and know damn well its probably only one *#!*!?# line of code!! )
PLEASE HELP BECAUSE i AM BECOMING OBESSESSED I THINK....VERY FEW PROBLEMS GO UNSOLVED IN THIS BRAIN....ATLEAST NOT THE ONES I SPEND TIME ON FOR NO APPARENT REASON OTHER THAN TO SATISFY MY CURIOSITY!!!
ps Please trust me when I say the right syntax was used to create and instantiate all objects (including those in the above code which you may not see declared) as well as the appropriate reference to a Word library.
Edited by - ahara on 4/25/2004 7:50:30 AM
VB Program Creating Multiple Word Reports.
I have a program that does multiple things. It opens simple text files, reads the contents, uses the contents to search an MSSQL DB, Generates Reports and finally emails out the reports to the people that need them.
I have everything completed EXCEPT... I can't figure out how to get the information that my VB 6.0 programs knows.. into a formatted word XP file.
Is this possible? And if so where would the best place to look into how to set this up correctly.
Creating A &"Data&" Report From Multiple Tables
I need to start creating data reports which extract data from over four different tables and produce an invoice as a result.
I've been considering extracting all the neccessary information and then entering this into record in a "invoices" table, while this does duplicate data it would allow for invoices to be reprinted easily.
Is this a bad idea? Would it be better to attempt to write a query using the dataenvironment which extracts from four plus tables?
I like the option of reprinting invoices when neccessary.
Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.
In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.
I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement
Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset
' Open Excel Connection
Set cn = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
' Open Access Connection
Set cnAccess = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
' Load ADO Recordset with Excel Sheet1Data
oRs.Open "Select * from [abc$]", cn, adOpenStatic
' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
please help me so that i can move forward...
Recalling Data From Multiple Fields In Multiple Tables
I am working on a fairly large project for recording client information into a db which has quite a few fields which are spread out among several tables. The code is already written that saves the data to it's appropriate fields in their appropriate tables.In this particular case, under the front tab of the form is the head person's personal information. On the third tab is information pertaining to the head person's dependents. The code I have written saves the head person's data as well as the data pertaining to that person'd dependents' to a table named [Person]. A few lines lower, those persons' IDs are also entered into an additional table called [Relation], in which are only 2 fields: Relation_Parent_ID and Relation _Child_ID. In another control's code, I recall the data of a given person's ID back into the form from which it was recorded. But how do I code it so that even the children's data is recalled when the parent's data is re-inserted into the form? The code to recall every piece of data that is saved for that person's ID, except for the children's data, is already written and functional. How can I cause the children's data, also, to be recalled when the parent's data is recalled?
Thank you very much for all your help
Edited by - Happieman on 6/11/2005 10:18:40 AM
Multiple .MDF Files With 1 Table OR 1 With Multiple Tables?
In the past, I justed just one .MDB (access) database file with multiple tables, but have run across several commercial programs that have multiple .mdb files with one table. Those tables are then all linked.
Is there reason to go one way or the other and why? If using multiple .mdb files, how do you create the links between the tables in different .mdb files?
Updating Multiple Tables With Multiple Recordsets
I am trying to update two tables at once, based on the same text box inputs I seem to come close but it will not update the second table. Here is my code:
Dim Rs As Recordset
Dim Rs2 As Recordset
Dim CN, CN2 As Connection
Private Sub cmdBack_Click()
If Not Rs.BOF Then
If Rs.BOF And Rs.RecordCount > 0 Then
Private Sub cmdNext_Click()
If Not Rs.EOF Then
Private Sub cmdRefresh_Click()
Private Sub cmdSearch_Click()
Rs.Find "LastName = '" & txtSearch & "'"
If Rs.BOF Or Rs.EOF Then
If MsgBox("The record you entered was not found, Do you want enter another name ?", vbQuestion + vbYesNo, "ID Check") = vbYes Then
txtSearch = ""
txtSearch = ""
'Fills in the data with the fields based on the found data
txtLastName = Rs.Fields("LastName")
txtAmount1 = Rs.Fields("Amount1")
txtAmount2 = Rs.Fields("Amount2")
Private Sub Command1_Click()
Dim ST As String
Rs.AddNew ' Add a new record
Rs.Fields("LastName") = txtLastName
Rs.Fields("Amount1") = txtAmount1
Rs.Fields("Amount2") = txtAmount2
'CurrentDB.Execute ("Update tblName2 Set Last_Name = '" & txtLastName & "',Amounta = '" & txtAmount1 & "',Amountb = '" & txtAmount2 & "'")
Rs2.AddNew ' Add a new record
Rs2.Fields("Last_Name") = txtLastName
Rs2.Fields("Amounta") = txtAmount1
Rs2.Fields("Amountb") = txtAmount2
Private Sub Form_Load()
Set CN = New ADODB.Connection
CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs = New Recordset
Rs.Open "Select LastName, Amount1, Amount2 from tblLastNames", _
CN, adOpenKeyset, adLockOptimistic
Set CN2 = New ADODB.Connection
CN2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"
Set Rs2 = New Recordset
Rs2.Open "Select Last_Name, Amounta, Amountb from tblName2", _
CN2, adOpenKeyset, adLockOptimistic
Set txtLastName.DataSource = Rs
Set txtAmount1.DataSource = Rs
Set txtAmount2.DataSource = Rs
txtLastName.DataField = "LastName"
txtAmount1.DataField = "Amount1"
txtAmount2.DataField = "Amount2"
I think it has to do with the adOpenKeyset, adLockOptimistic at the end of the connection string, but if it is this it will not let me take it out or chage it. I also tryed moving things around and that did not woprk.
If any one has any ideas, of waht I am doing wrong, I would really appreciate it
Same Reports Different Tables
Crystal Reports 9
This is my problem, as I have faced it MANY times and quite frankly it is a LOT of work to do for what I need. Hopefully there is a way around this.
I create reports that are based on each other, every report is EXACTLY the same with one difference, the tables. Say I have 3 reports, all have the exact same layout, report 1 gets info from table 1, report 2 gets info from table 2, etc. All I want to do is change all the fields on the second report to the table 2 fields. How can this be done? The tables are identical in structure, data is only difference.
Printing Reports From Three Tables
Hi, I want to design a report in Visual Basic 6 from Three Tables. One of the tables contains all the data and the other two tables are normalised from the first table to make the data more meaningful.
How do I go about this...I am a beginner programmer
SQL With Multiple Tables...
Hi...I was just wondering...
I have three tables...Agencies, ProductsOrdered, and Orders.
I have 5 listboxes representing 'Monday' to 'Friday'...what I need to do is go into the database and retrieve the Agencies...plop it into these listboxes according to the day the Agencies place and order which is in the ProductsOrdered table. My question is...do I have to go into the ProductsOrdered table, get the OrderID for orders place through Monday to Friday, open up the Orders table, match the OrderID from the ProductsOrdered table to the OrderID from the Orders table, get the AgenyID from this table and open up the Agencies table and finally get the Agency and populate it into the listboxes!!! This is the long way of doing it, can I just SQL with InnerJoin to do all of this? I hope I'd explained this well enough!!!
What is the syntax for a select statement that searches through multiple tables?
I was wondering, is there anyways to pull out data from 3 tables? if there is how?
Hello, I have a design problem. What I am doing is creating n-tear application with ASP.NET using VB and using WEB Services.
My web service is responsible for creating connection object to Oracle database, and retrieving information and put it in to dataset. SQL statements is provided by business module class that is calling web service. This information is then displayed in the datagrid. Once changes are made this dataset gets posted to web service for update/insert/delete.
In most of cases these SQL statements will be connecting multiple tables with WHERE a.pk = b.fk etc…
What is the best way to load this data in to data set, so I can use commandObject to automatically post them back to database? Should I load data in to several tables inside of one dataset with separate SELECT statements? What about displaying multiple tables in the datagrid? Parent-child?
Please help me with this design logic, and with some sample code, for this process.
Edit by herilane: Split from hereHi,Supa-Fly,
It is very helpful your last post to my project now.
But i encountered a big problem now.....
In my project i need to create around 50-100 tables in the document.
I have problem to create mutiple tables..
In my program i coded like this to create multiple tables:
'create the nTH table
Call AddTable(oDoc, oRange, n, 9, 2)
'add space between the tables
oDoc.Tables(n).Select 'select the table we just create
Selection.MoveDown Unit:=wdLine, Count:=2 ' move the cursor down
Selection.TypeParagraph 'add two blank paragraphs
Set oRange = Selection.Range 'define new oRange
'create the (n + 1)TH table
Call AddTable(oDoc, oRange, (n+1), 9, 2)
The problem is that my codes can not always excute well.
Sometimes, the next table will be created inside the cell of the previous table.
If change the number of "Selection.TypeParagraph", there will be error-free, or the error will occur at another place...
And this error will stop the excution of the program.
It seems impossible to generate such error from my codes.
Is there any good idea to add space between two tables....
Really appreciate your helpful reply........
I have to submit the project this friday and do the Demo also....
Many thanks to you, ...........
I'm trying to write several macros to create tables with particular attributes in the individual cells (font size, bold, width etc).
This seems to work but I need the tables several times in the same document. When I use the macro a second time it creates a new table but the formatting (font size ...) is applied to the first table not the new one.
How can I code the macro so the formatting applies to the new table and not the original?
I hope my question is clearly explained, I can show the code if it helps
(but haven't here because of space).
Creating SQL Tables With VBA
I'd like to create tables with VBA. Anyway it doesn't seem to work when I try to add variables in it.
Do While Not .EOF
DoCmd.RunSQL "CREATE TABLE " + !variable + " (blah blah);"
This just gives me syntax error.
Hi everyone.... guess my question is being asked before but please bear with me... i need to create a new table in an existing database after clicking on a button... searched the forum and most of them includes ADOX and DAO.... is it a must to hav those or can i use ADODB or data control?
Btw... i am using VB6 and Access 97.... hope u all can help.... Thanks a lot...
Creating Tables In VB
I am doing a project on payroll with vb as front end & access as back end.
I want to create individual tables for every new month using the front end only.
The user should be able to create a new table for the month of
October 2002. The table name should be October2002
How to create the table using Visual basic in Access ?
can i use the adodb.command property to create the table at the back end ?
I need to be able to create a table with a set amount of columns. I have the following code;
'This example demonstrates creating a Jet table with
'Autoincrement, Decimal, and Memo columns.
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
'Return Reference to current database.
Set cat.ActiveConnection = DBname.Connection
'Assign the new table name.
.Name = TableName
' Append new columns to the table.
.Append "1", adLongVarWChar
.Append "2", adLongVarWChar
.Append "3", adLongVarWChar
.Append "4", adLongVarWChar
' Append new table to the provider catalog and clean up.
Set cat = Nothing
Now I have highlighted some code in red. Is it possible to change it to the following code??
u = 1
for i = 0 To Varibale
.Append "'u'", adLongVarWChar
Can some one point me in the right direction if it is wrong???
Creating Tables In VB For SQL
I have a little problem here, I am working in VB 2005 express, I can't figure out how to get the right calculation in VB to give me 3 days after due dates I'm connected to SQLexpress and in SQLexpress I have a table called contracts I wrote a codes that generated the dates that i needed in VB to imports in SQL. but now i need to know how can i get VB to recognize the contract column and give me 3 days after the contract column... I know this sound confusing
I already have column1 figure out - but how do i get column3 to recognize the dates in colume1 and give me 3 days after that day?
Creating Tables With VB6
How do I create a table in access using VB6
Currently I've created the table, but there's no column in it.
How do I resolved this issue? I've searched the forum but couldn't find anything thats solves my problem
'Add new table/group to databasePrivate Sub cmdCreate_Click() 'Declared variablesDim dbIP As New ADODB.Connection 'Open connection to databasedbIP.Open "dsn=NCMSdB" 'Query databasesql = "Create Table " & txtGrpNm.Text & "[(GRP)]" 'execute sqldbIP.Execute sql 'Close databasedbIP.Close End Sub
DAO Creating Tables From 1 DB To Another
Does anyone have the code that will delete and then create tables an one database using the tables from another database?
In other words...
Start with a Export.mdb database,
run a process that reads through the tables in a full database (Main.mdb)
the process should take the name of Main.mdb tables and use that name to create the table in Export.mdb.
If the table already exists in Export.mdb, then delete it before creating the new table.
Thanks ahead of time!!
Creating Tables In The DB
I have never used a command object before and my book says that I should use it when trying to create tables in my DB. Never heard of it before, let alone know how to use it. Does any one have any code examples on how to create a table in the DB? Thanks for any help.
I need your help again.
I have build a program using DAO and an Access database. This works well. I could say great.
Now the problem:
The company using this program has found out that working with 50 people in 1 Access db isn't fun. So they want to Upgrade to a SQL server.
In most of the code this isn't any problem, but I have never made a new table in a database using ADO.
Can someone show me how to do this.
Just for the record, these databases aren't copies of the Access DB, but there new tables that have never existed before.
Creating Tables With ADO
I am here with anopther easy(!) question for most. What am I going to select from references to creat Access Table with ADO 2.7?
Thanks a mil...
Crystal Reports In VB App, No Tables Found
I have a new problem with the same app since I was last here in July. Please, someone help.
I am opening a Crystal Report from VB. It opens fine. Here is the code that works so far:
Set crApp = New CRAXDRT.Application
Set crApp = CreateObject("CrystalRuntime.application")Set oCDO = CreateObject("CrystalDataObject.CrystalComObject")
Set crRpt = New CRAXDRT.Report
Set crRpt = crApp.OpenReport(DirPath & "DSR_With_Work_Tables_Batch_Prod.rpt")
crRpt.PaperOrientation = crLandscape
Okay, up to this point everything is cool. I had problems before with permissions and connecting to the tables, but that was solved with the following code:
For i = 1 To crRpt.Database.Tables.Count
crRpt.Database.Tables(i).SetLogOnInfo "", "", "sa", "white"
That worked beautifully - until they reloaded the Operating System on our test machine. (all of these problems, by the way, have been on the TEST machine.)
Anyway, problems started again, with the program freezing. By putting some hokey error messages in, I discoved that when it got to this line
For i = 1 To crRpt.Database.Tables.Count
that the Tables.Count was now = 0 ' (ZERO !!!! AHHHHHH!!)
So of course it couldn't set the permissions on the tables because it wasn't finding them !!! AHHHHHH !!!
The program's ODBC connectivity is working fine. It is writing calculations to that same database. I have double-checked the report to make sure that it is pointed to the right database. (And it is openig the report!) I have opened up the ODBC.dsn in notepad to make sure it's correct. I have walked through the ODBC and tested the data source.
I don't know what's wrong, and I don't know what to do. I am thinking at this point that I need to somehow point that report to the database source, but I am floundering through the different options by doing this
and at this point I get confused. I don't know if I am on the right track.
Please, someone help.
In Desperate Need,
Grouping From 3 Tables (Crystal Reports)
Imagine the three tables
Would it be possible to group these into one report?
For example, one record might be:
- Combine Harvester
Using Crystal's grouping, I can get farmers plus equipment, or farmers plus animals, but not all three at once. Perhaps there is some special way of doing that?
Replace Tables From Crystal Reports In Vb
I want to make a form that I can replace entire tables of information depending on an option chosen in vb.
So I have a form with a 2 paragraph writeup with certain fields (like Name) that need to be replaced depending on which student (from a list) is chosen in vb. After the writing, I need to have table(s) with bulleted information in them. THe tables displayed here are dependant on what is selected in vb.
So if table A and table C is selected (from a list of tables A, B, C, D, E, F), it would display those tables at the bottom of the form.
Is this possible. I know my explanation is kinda confusing, so if you have any questions, just ask. If someone can help or maybe direct me to a tutorial, that would be great. I have never used Crystal Reports before, so treat me like a 3 yr old monkey when explaining things.
Multiple Tables Summary
Here is my problem. I have an excel file with multiple work sheets. In each work sheet i have a table with the following fields: Name, Surname, ID_number. All that I want is to be able to make a new work sheet in which i can generate automaticly a table which contains all students (Name, Surname) who have an ID_number bigger than 100.
PLEASE HELP ME
Self Join With Multiple Tables
Hi, Ive a working Query...
SELECT dbo.TblLookupPreferences.ContactType, dbo.TblEmailAddress.Email, dbo.TblEmailAddress.Name, dbo.TblEmailAddress.Title,
FROM dbo.TblAdmMain INNER JOIN
dbo.TblLookupPreferences ON dbo.TblAdmMain.AdminID = dbo.TblLookupPreferences.AssociatedID INNER JOIN
dbo.TblEmailAddress ON dbo.TblLookupPreferences.LookupValue = dbo.TblEmailAddress.EmailID
WHERE (dbo.TblLookupPreferences.ContactType = '7') AND (dbo.TblEmailAddress.DoNotMail = 'N') AND (dbo.TblLookupPreferences.AssociatedType = 'A')
But Now I want to add another table to the same view..
The Other TableName is 'TblProspectmembers'
In need to join tblprospectmembers [Memno] field =
TblLookupPreferences.AssociatedType = 'M'
but I cant figure out the exact syntax for this.
any help would be highly appreciated!
(oh im using Sql server 2000 and Access 2000 as a front end)
Getting The Count Of Multiple Tables At Once
Is there any way we can get the total count of multiple tables with one sql statement? Something like this:
Select count(Video_ID) As VideoCount, count(Customer_ID) As CustomerCount from Video, Customer
Obviously this doesn't work, I was wondering if there was any correct way of doing this. I would appreciate any help. Thanks.
Reading From Multiple Tables
I can't figure out why my program cannot read records from a certain table in my database. I have 3 related tables that I want my VB program to pull data from. I have 2 of them pulling in fine, however the third table comes up with an error message "No Current Record". I have set up multiple data controls, one for each table, and I am using the seek mehod of the Data Control to connect the controls, using the primary keys in the table. Could someone please look at my code, to double check my work? Maybe I'm overlooking something.
Private Sub Form_Load()
dtaStateLookup.Recordset.Index = "PrimaryKey"
dtaAppointments.Recordset.Index = "PrimaryKey"
Private Sub dtaContactInformation_Reposition()
dtaAppointments.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("ContactID")
If dtaAppointments.Recordset.NoMatch Then
TxtDate.Text = "****"
dtaStateLookup.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("StateAbbreviation")
If dtaStateLookup.Recordset.NoMatch Then
TxtState.Text = "****"
Problem With Multiple Tables
When I open an ADO recordset with JOINs, I couldn't delete (with .delete method) records in the primary table. It gives "Insufficient Column .....". Moreover, on updating value of the key field, the values of the other fields of the secondary table would not be refreshed.
Could anyone give the solution to these problems?
Update Multiple Tables In MS Sql
I want to update two tables with one query.
Instead of running two queries like this...
update contact set contact_id=2 where contact_id=4
update appointment set contact_id=2 where contact_id=4
I want to run the above queries in one query. Is this possible??
Thanks in advance.
How Do You Make Multiple Tables
I used the below code to make a database with one table. Tried with no success to make an additional table by duplicating the table creation code. Question: How do I create a second table. Also, how do I index it? - Steve
Private Sub builddb()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
CurDir & "airmen_test.mdb"
Dim objcat As ADOX.Catalog
Dim objMyTable As New ADOX.Table
Set objcat = New ADOX.Catalog
objcat.ActiveConnection = strConnection
.Name = "tbl_dom_info"
.Columns.Append "first", adVarWChar, 20
.Columns.Append "middle", adVarWChar, 20
.Columns.Append "last", adVarWChar, 20
Set objMyTable = Nothing
Set objcat = Nothing
Select From Multiple Tables
I have three tables:
phone_types: idtype, description
phone_object: id_object, description
phone_number: familyid, idtype, idobject, phone_number
When I choose a family: I want to be able to display
type(description) | object(description) | phone_number
but now on a flexgrid i'm only able to display :
idtype | idobject | phone_number
- is using a flexgrid a good idea?
- how do I change my select?
Select From Multiple Tables
I have two tables in an access database that have a 1 to 1 relationship. I want to pull out all fields from the two related records. Something like this:
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = Charges.ClientID"
Except this always gives me the last record created which I dont want. I want a specific record. I have the ID number of the record I want in a variable called IDnum. How do I get all fields from both tables for that specific ID number (IDnum)?
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = " & "' IDnum"' & " = " & Charges.ClientID" (A horrible attempt at guessing) or something like that?
V-lookup Using Multiple Tables
I am looking for some direction on how to solve the following problem...I have a database with 2.3Million line items that I map in several different ways and pull over into an excel pivot table using an OLAP cube. I would like to use a string of v-lookup statements to pull from the various pivot tables. The formula quickly exceeds the size limit for the cell because each cell needs to pull five items the pivot tables.
For example: I want items E4000, 60000, 100ZZ, and X50 to pull into the cells calculation but each of the items is located in a different table. The first digit tells me which table the information is in. The tables are too large to be joined into a single table.
What I was wanting to do but as a VB novice I need some direction is to define the lookup ranges in VB and to reference the range so that I can save space. Is there a way to do this or to define the entire function in VB?
Thanks for the help
Updating Multiple Tables
Hi guys, I need some help please. I have a form which is suppose to record receipts of goods. I am using a datagrid for the data entry process. The form needs to access records from four tables. I have used SQL to do that. After that i am stuck. I would like to have the datagrid automatically put in the Autonumber of the PurchaseInvoice andi would also like the other tables to be updated with the new info. Any help would be appreciated.
Private Sub Form_Load()
Dim rSql As String
Me.Height = 7000
Me.Width = 12000
Set stockCon = New ADODB.Connection
stockCon.ConnectionString = _
"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:My DocumentsGP projectgpframings.mdb"
Set stockRS = New ADODB.Recordset
'Prepare the recordset.
rSql = "SELECT purchaseinvoice.purchaseinvoiceno, purchaseinvoice.purchaseinvoicedate, purchaseinvoice.purchaseorderno, stock.stockno, purchaseinvoicestock.quantity, purchaseinvoicestock.cost, purchaseinvoice.subtotal FROM ((purchaseinvoice INNER JOIN purchaseinvoicestock ON purchaseinvoice.purchaseinvoiceno = purchaseinvoicestock.purchaseinvoiceno) INNER JOIN purchasesupplier ON purchaseinvoice.purchaseinvoiceno = purchasesupplier.purchaseinvoiceno) INNER JOIN stock ON purchaseinvoicestock.stockno = stock.stockno"
.ActiveConnection = stockCon
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
If stockRS.BOF And stockRS.EOF Then
Set tranGrid.DataSource = stockRS
Multiple Tables In Access
how can I open an Access Db and be able to viewedit all the tables??
The only way I can do it is to open each one separately.
here is my code
'the name of my table is: Incident
' i do not see how it reads the particular table in the DB
'this code works
Public rsIncidents As ADODB.Recordset
Public CN1 As ADODB.Connection
Public CMD As ADODB.Command
Set CN1 = New ADODB.Connection
CN1.CursorLocation = adUseClient
CN1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & "c:mydbicd02.mdb"
CN1.Properties("jet OLEDB:Database Password") = "charlyX"
Set CMD = Nothing
Set CMD = New ADODB.Command
CMD.ActiveConnection = CN1
'Create a query to retrieve all fields from the Incident table, sorted by IncidentDate Ascending
CMD.CommandType = adCmdText
'Db sort mode selected here
CMD.CommandText = "SELECT * FROM Incident ORDER BY INCIDENTDATE"
Set rsIncidents = New ADODB.Recordset
rsIncidents.CursorLocation = adUseServer
rsIncidents.CursorType = adOpenKeyset
rsIncidents.LockType = adLockOptimistic
Set rsIncidents.Source = CMD
'to read a field of the DB i would use code like this
'EntryPerson is a field in the DB
'rsIncidents("EntryPerson").Value & ""
Multiple Tables From One Table
My simple VB 5.0 app uses a large table with many fields. I would like to use many smaller, linked tables for program info, user info, dimension info, etc. for better organization and easier handling. I can create the tables, but how I link them and modify the code below to add new records?
Public Sub RECORD_ADD()
Data1.DatabaseName = "C:ID-VISUAL-BASICsimple-progsp.mdb"
Data1.RecordSource = "USER-INFO"
Data1.Recordset("USER-Application") = Mainform.LabelAPP
Data1.Recordset("USER-Description") = Mainform.TxtDescribe.Text
CmdSave.Enabled = False
SQL Satement From Multiple Tables
I've got a DB with a Product table and a Campaigns table that describe two types of product lines.
In a search page, I need to be able to search by a given ID #, which is chrProductId in the product table, or campaignCode in the Campaigns table.
I use LIKE statements, so if someone searches for 100, they get all results that have '100' somewhere in their product id.
SELECT * FROM PRODUCT p, CAMPAIGNS c WHERE c.campaignCode = '15-15001' OR c.campaignCode = '15-15001'
but of course, this will give all the results from both tables whenever one of the conditions is met (in this case, 15-15001 is a campaign code, so there should be no results from the product table portion, just the campaign results). For a partial search string (like "15"), you could get results from both tables.
MsHflexgrid... Multiple Tables...
i was wondering if it is possible to use this:
If cboContact.Text = "-Alle-" Then 'if i must... get ALL the data...
strSQL = "SELECT * FROM " & cboContact.List(1)
For i = 2 To (cboContact.ListCount - 1) 'the other tables...
strSQL = strSQL & " UNION SELECT * FROM " & cboContact.List(i)
strSQL = strSQL & " ORDER BY VerloopDag ASC,VerloopTermijn ASC "
Else 'or... just select from the selected TABLENAME...
strSQL = "SELECT * FROM " & cboContact.Text & _
" ORDER BY VerloopDag ASC,VerloopTermijn ASC"
MyRecSet.Open strSQL, MyConn, adOpenStatic
Set MSHFlexGrid1.DataSource = MyRecSet
when i execute...
and click on "-Alle-" in the cboContact (combobox)...
it gives me nothing...
- when i select "-Alle-"...
- strSQL = "SELECT * FROM tom UNION SELECT * FROM tombo ORDER BY ...."
thats all good, right?
then why wont it work??