Split Table - have been looking for answer

[JoJo6205]

Hi all,



Not sure if I'm in the correct forum so I thought I'd start here.

I have a large table in Access that needs to be exported into Excel however, it's too big for Excel so it needs to be split up. The best way to split it is by DC Name. What I'd like to do is find all ABQ records and move all data for ABQ to another table. I have 30 different DC Names and not sure if splitting one table into 30 tables is the best option. Another option would be to create a macro that would export the Access data into 30 different Excel spreadsheets or workbooks according to the DC Name field.

example:

Report Date	DC Name	Product #	PO Date
2/17/15	ABQ	ABCDE	6/5/14
2/17/15	BDR	JKLMN	9/10/14
2/17/15	ADD	PQRST	12/15/14

If you have a better suggestion, I'm all ears.

Thanks so much for your help.

[lfpm062010]

This is my opion.

I would leave the data in one table. It does not matter if you have 30 different or 100 different DC Names. What you do is create macro/VBA code and feed your DC Name into it and export that into Excel spreadsheet.

[JoJo6205]

I accidentally deleted a line. The table in Access has more records than Excel can handle therefore I need to split the table in Access. I figured if I have to split it once, maybe I can split it all at the same time and not have to do it in Excel.

[JoeM]

You definitely don't want to split your Access table - leave it as one table.
You can "split" your data in Queries, for the purposes of exporting them to Excel. You can use Query Criteria to tell it which records to include in each Query.

[JoJo6205]

JoeM,

Please don't think me ungrateful. I haven't tried this yet as I've had another project to work on. I'll let you know how it turns out. Thanks for your help.

[JoJo6205]

You definitely don't want to split your Access table - leave it as one table.
You can "split" your data in Queries, for the purposes of exporting them to Excel. You can use Query Criteria to tell it which records to include in each Query.

Hi JoeM,

Been thinking about the query split and it is a much more sound approach. I found a a SQL code that works but I can't figure out how to modify for my needs. I also tried the DLookup function and it works beautifully but again, I can't quite figure out how to modify it for my needs.

To reiterate, I have 606 Vendors (which has the potential to grow) and they each need their own spreadsheets regarding open PO's. Here is a sample of what I'm working with:

DC Name	PO#	Supplier	Product Number	PO Due Date	Priority
ABQ	568974	Company 1	01254	2/25/15	1
ABQ	DD6598	Company 2	02087	3/1/15	2
BDR	6594	Company 1	01254	3/28/15	1
CTV	E65G6	Company 3	65987	3/1/15	3
DEN	6598	Company 4	5979	3/26/15	0

The goal is to have the records exported by Supplier into Excel and end up in either their own workbooks or worksheets, whichever is easier.

The code I found splits a table during export and uses a number range. I tested it with my Priority column and it worked but I don't know how to make it work with text unless I type in each Vendor separately. This is the code:

Option Compare Database
Option Explicit


Sub ExportToXlsx()
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb


Const xlsxPath = "C:\Users\u138044\Documents\test files\foo.xlsx"


' create .xlsx file if it doesn't already exist, and add the first worksheet
Set qdf = cdb.CreateQueryDef("mySheet1", _
"SELECT * FROM FBKO_Supplier WHERE Priority Between 0 And 1")
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "mySheet1", xlsxPath, True
DoCmd.DeleteObject acQuery, "mySheet1"


' file exists now, so this will add a second worksheet to the file
Set qdf = cdb.CreateQueryDef("mySheet2", _
"SELECT * FROM FBKO_Supplier WHERE Priority Between 2 And 3")
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "mySheet2", xlsxPath, True
DoCmd.DeleteObject acQuery, "mySheet2"


Set cdb = Nothing
End Sub




Another option I've been trying to get to work is DLookup in the query. Again, I don't know how to make it work to separate the data by Supplier unless I use a parameter and enter the parameter 606 times. Here is the function written in the table:

DLookUp("[Supplier]","Master Winter Schedule","Supplier ='Company 1'")



I hope my explanation is clear.

[June7]

Code can open a recordset of vendor IDs and cycle through the recordset to set QueryDef or open a filtered report, export, then move to next vendor, repeat.

I would prefer exporting a simple filtered report than creating and deleting queries.

Another alternative is to have the query reference an unbound textbox on form as parameter and then the code sets textbox with ID value from the recordset.

[JoJo6205]

Thanks! I just realized I accidentally posted this question twice and someone answered on the other post as well. I'm going to take all the info and see what I can do with it. I appreciate your input.