Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 15th, 2005, 03:19 AM
Authorized User
 
Join Date: Mar 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exporting related tables to excel

Hello! I have to export two related tables (EmployeeData (ID,Name,Age) and PayingDate(ID,Income,Date) from Access to Excel.

The excel table should look like this:
1 John 35 ----->dates from EmployeeDate
  1400 15.05.2004 ----->dates from PayingDate
  1600 15.06.2004 ----->dates from PayingDate
  1145 15.08.2004 ----->dates from PayingDate
2 Mike 46
  2200 14.08.2004
  2100 15.09.2004

......... etc.



It's obvious i can't use TransferSpreadsheet in this case, so please help me with other ideas.


Reply With Quote
  #2 (permalink)  
Old September 15th, 2005, 03:43 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hiya Crisan,

Try creating a crosstab query. Once you get the data back the way you want, you can then export it to excel.

Kevin

dartcoach
Reply With Quote
  #3 (permalink)  
Old September 15th, 2005, 04:11 AM
Authorized User
 
Join Date: Mar 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

kevin, i tried to make the case as simplest as possible. In fact, I have to do with more complex tables, with many fields and a lot of grouping levels. I need the VBA code for this simple case just to understand the basic concepts of exporting to excel. After that, i will try to adapt the code on my real case. Thanks.

Reply With Quote
  #4 (permalink)  
Old September 15th, 2005, 04:15 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Crisan,

Here's some sample crosstab query sql:

TRANSFORM Sum(EmployeeData.Age) AS SumOfAge
SELECT EmployeeData.Name, EmployeeData.Age, PayingData.Id, PayingData.Income, PayingData.Date
FROM EmployeeData LEFT JOIN PayingData ON EmployeeData.Id = PayingData.Id
GROUP BY EmployeeData.Name, EmployeeData.Age, PayingData.Id, PayingData.Income, PayingData.Date
PIVOT EmployeeData.Id;

Once you've created the query, you can use it in the transferspreadsheet command just like a table.

Kevin

dartcoach
Reply With Quote
  #5 (permalink)  
Old September 15th, 2005, 04:53 AM
Authorized User
 
Join Date: Mar 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kevin, first of all, thank you for efforts to help me.
Ok, with this crosstab I have all the information I need. This is good. The major problem is that I need to export this information into a simple and easy to understand excel file.
In the excel file , I need a main line with user personal data, followed by each line with Income and Date value for this user. Between users must be two blank lines.
I don't know if I could do this with TransferSpreadsheet. Please let me know if is possible.

Reply With Quote
  #6 (permalink)  
Old September 15th, 2005, 06:18 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Crisan,

I hope you can understand this. It will do what you want.

   Dim rs As Recordset
   Dim db As Database
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Query2") 'Name of your Crosstab query
   With rs
      .MoveFirst
      myID = !ID
      mycount = 1
      Do While Not .EOF
         If mycount = 1 Then
            mysql = "Select " & """" & Str(!ID) & """" & " as ID, " _
                  & """" & !Name & """" & " as Name, " _
                  & """" & Str(!Age) & """" & " as Age into Paydata;"
            DoCmd.RunSQL mysql
            mycount = mycount + 1
            mysql = "Insert into Paydata (ID, Name, Age) " _
                  & "Select " & """" & "" & """" & " as ID, " _
                  & """" & Str(!Income) & """" & " as Name, " _
                  & """" & Str(!Date) & """" & " as Age;"
            DoCmd.RunSQL mysql
         Else
            If myID = !ID Then
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & "" & """" & " as ID, " _
                     & """" & Str(!Income) & """" & " as Name, " _
                     & """" & Str(!Date) & """" & " as Age;"
               DoCmd.RunSQL mysql
            Else
               For I = 1 To 2
                   mysql = "Insert into Paydata (ID, Name, Age) " _
                         & "Select " & """" & "" & """" & " as ID, " _
                         & """" & "" & """" & " as Name, " _
                         & """" & "" & """" & " as Age;"
                   DoCmd.RunSQL mysql
               Next I
               myID = !ID
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & Str(!ID) & """" & " as ID, " _
                     & """" & !Name & """" & " as Name, " _
                     & """" & Str(!Age) & """" & " as Age;"
               DoCmd.RunSQL mysql
               mysql = "Insert into Paydata (ID, Name, Age) " _
                     & "Select " & """" & "" & """" & " as ID, " _
                     & """" & Str(!Income) & """" & " as Name, " _
                     & """" & Str(!Date) & """" & " as Age;"
               DoCmd.RunSQL mysql
            End If
        End If
        .MoveNext
    Loop

   End With
   Set rs = Nothing

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PayData", "C:\PayData.xls", True

Hope this helps!

Kevin


dartcoach
Reply With Quote
  #7 (permalink)  
Old September 15th, 2005, 06:24 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Crisan,

I forgot, when you open the Excel file, since I had to convert numbers to text to insure that you could have both numeric and text and null all in the same column, you will have to convert the cells that are text but have numbers in them back to number format.

Kevin

dartcoach
Reply With Quote
  #8 (permalink)  
Old September 15th, 2005, 07:48 AM
Authorized User
 
Join Date: Mar 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 Thanks, Kevin! It looks like a good starting point. I will try to adapt this to my needs.
 btw, which is the safest way to prevent sorting of the PayData table? I want to avoid the accidentally scrambling of data from this table, because a lot of users will have rights for it.

Reply With Quote
  #9 (permalink)  
Old September 15th, 2005, 07:53 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Crisan,

Since the PayData table is created each time you execute this process, you can delete it after the export if you want. Since you have the spreadsheet you no longer need the table.

By the way, where are you located in the world?

Kevin

dartcoach
Reply With Quote
  #10 (permalink)  
Old September 15th, 2005, 09:28 AM
Authorized User
 
Join Date: Mar 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Kevin, I'm from Romania.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
single submit to related tables forumuser BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 December 2nd, 2007 10:29 AM
Synchronising Two Sets of Related Tables Odeh Naber Access 11 May 14th, 2007 06:58 AM
Insert record Into 2 related tables at once kalchev ASP.NET 2.0 Basics 2 May 9th, 2006 05:10 AM
Insert on 2 Related tables question acdsky Classic ASP Databases 1 July 21st, 2004 09:29 PM
transformation on set of related tables? peckli SQL Server DTS 1 March 3rd, 2004 12:21 PM



All times are GMT -4. The time now is 04:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.