Solved

Combine Tables Access 2010

Posted on 2011-02-11
9
2,172 Views
Last Modified: 2012-05-11
I have an Access 2010 database.  There are 15 tables.  Each table is named for the product.  Each table has the following fields:  Product Size and Product Cost.  The issue is that many of the products have the same size (12x48).  I need to create something so that one person can access and update all the prices easily from one area instead of opening up each table.  I tried to do a form, but because the fields in each table are the same, it requires a relationship, but each product is NOT related. I am fine with recreating the database, just not sure what best method there is for this.
0
Comment
Question by:lancecurwensville
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34872814
you can create a new table  using union query

select * into NewTable From
(
select [Product Size], [Product Cost], 'tableName' as ProductName from tableName
union all
select [Product Size], [Product Cost], 'tableName' as ProductName from tableName


)


change "tableName" with actual names of tables


0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
ID: 34872829
You could make a form with a combo box in the header for selecting the product table, which would then display that table in a subform for editing.  I use this method for editing lookup tables.  See my Access Archon #198 for an article and sample database.  

Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch198.zip
Here is a screen shot of the form:
Lookup-Table-Edit-Form.jpg
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34872847
For quickly updating the prices, you might be better off using code.  You can make a union query for viewing the data, but union queries are not updatable.
0
 
LVL 21
ID: 34873695
What you are doing with the 15 tables is more like an Excel workbook than a relational database.

All the products really should be in a single table. Currently you are using the table name as part of the data. You should add an additional field to the table to hold this data.

Once you get all the data into a single table things will get a lot easier to do.

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Author Comment

by:lancecurwensville
ID: 34874003
@thehitechcoach:  I agree completely, however, the purpose of the database is to allow for mail-merge quoatations in Word, and I did not see an easy way of separating the products and other items on the merge other than to utilize different tables.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34874035
<and I did not see an easy way of separating the products and other items on the merge other than to utilize different tables.
>

with creation of the newTable i suggested at http:#a34872814

you can easily separate products using a query

select * from newTable where ProductName="myProduct"

0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 34874419
lancecurwensville,

I agree completely, however, the purpose of the database is to allow for mail-merge quoatations in Word, and I did not see an easy way of separating the products and other items on the merge other than to utilize different tables.

That is simple, use a query to filter out the records you need.  You can even simulate having 15 tables by creating 15 queries to filter the data like it was in the separate tables.

What happens if you add a new product? You will have to add a new table and lots of extra work.

I do lots of mail merging with Word. The best way I have found to do a merging  is to never let Word connect to your Access database. I will export out the data to a word merge document format.and merge with this file.  The issue with Word merging with an Access database is that is opens an additional instance of Access in memory to to open the database. This can cause lots of issues.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34874469
<You can even simulate having 15 tables by creating 15 queries to filter the data like it was in the separate tables.>

you can use ONE query, use a combo box in a form to filter the query for the product you want to extract from the NewTable.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34898728
For a mail merge (done in code), you could create filtered queries to create recordsets, and (if necessary) run make-table queries to create temp table for export.  Here is some sample code:
[sample code fragment using the procedure]

   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim rst As DAO.Recordset
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   
   strRecordSource = "tblInventoryItemsComponents"
   strQuery = "qryTemp"
   Set dbs = CurrentDb

   'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   If lngCount = 0 Then
      strPrompt = "No records found; canceling"
      strTitle = "Canceling"
      MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
      GoTo ErrorHandlerExit
   Else
      'Use this line if you need a recordset
      Set rst = dbs.OpenRecordset(strQuery)
   End If

   'Use SQL string as the record source of a form
   strFormName = "fpriLoadSoldPackingSlip"
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acDesign
   Set frm = Forms(strFormName)
   frm.RecordSource = strSQL
   DoCmd.OpenForm FormName:=strFormName, _
      view:=acNormal
   
   'Use SQL string as the record source of a report
   strReport = "rptLoadSold"
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewDesign, _
      windowmode:=acHidden
   Set rpt = Reports(strReport)
   rpt.RecordSource = strSQL
   DoCmd.OpenReport ReportName:=strReport, _
      view:=acViewNormal, _
      windowmode:=acWindowNormal

   'The report has the filtered query as its record source

=========================

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Error compiling fairly simple database 3 16
Recording mileage on a form 2 11
putting an icon in a form 13 21
Dlookup issue 4 5
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now