Solved

SQL help needed for MS ACCESS

Posted on 2013-11-20
4
443 Views
Last Modified: 2013-11-23
Hello Experts,

I need help writing some SQL statements.
I’m using Access 2K with a split front and back end.
The front end is called Inventory_Program and the back end is called Inventory_Data
I also have an external access 2K database called Inventory_XMITFILES

I need to write code that executes from the front end that creates three files in the external database.

Table 1 would include all the items in my Parts4Sales table and include the following fields: QTY, MPN, MFG, DC.  I would like to name the new table Stock_ALL

Table 2 should include all the items in my Parts4Sales table, however the QTY field should be summed when the other fields (MPN,MFG, and DC) are identical.

Table 3 should include all the items in my Parts4Sales table, however the QTY file should be summed when only MPN and MFG are identical.  The DC field must be included in the new table, however the word CALL should replace the actual data in the DC field.

For example, if the following data appears in the Parts4Sales table:


QTY      MPN      MFG      DC
10      123      ABC      2012
15      123      ABC      2012
20      123      ABC      2013


TABLE 1 should look just like the table above.  Four fields and three rows.
TABLE 2 should have two rows listing a QTY of 25 and the second row listing 20.  Other fields would include the MPN, MFG, and DC data
TABLE 3 should have only one row with QTY of 45 and displayed with the existing  MPN and MFG data, and the third field DC should include the word CALL

The name and location of the external table is saved in the variable strDBpath

Its also important that the external tables have include the same field names and the fields must be in the same order.
0
Comment
Question by:pcalabria
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Here's a procedure from one of my apps.  You need to build the strFileName variable and then use a DoCmd.TransferDatabase acExport to create the table.  As you can see, some of the sources are tables and others are queries.  The queries are used because I need to control what data gets exported.  I don't want to export it all.

Public Sub ExportTables(strFileName)
    'Export data to new mdb file

    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportReviewStatus", "tblReviewStatus", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportStates", "tblStates", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportStatus", "tblStatus", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportRoles", "tblRoles", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportUserRoles", "tblUserRoles", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportUsers", "tblUsers", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportDocuments", "tblDocuments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportAuditParms", "tblAuditParms", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportMembers", "tblMembers", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportRefDocs", "tblRefDocs", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportComments", "tblComments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportDependents", "tblDependents", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportHelpComments", "tblHelpComments", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportListValues", "tblListValues", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportLtrSent", "tblLtrSent", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "qExportVerificationPhase", "tblVerificationPhase", False
    DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "tblTPA", "tblTPA", False
End Sub

Open in new window

0
 

Author Comment

by:pcalabria
Comment Utility
Thanks Pat, but when I see code like this I realize how little I really know about Access.  I'm totally confused and don't have a clue how to use the code above.

I had no trouble creating the first table using a MAKE TABLE QUERY, so this part of the problem is actually solved.

My code for the second table works as I would hope, except that it requires I change the name of the quantity field, which would be a problem.  The new field created in the external database must be named Quantity.

I'll be 2/3 of the way there if you, or any of the other experts can tell me how to fix the uploaded SQL.

I'd was expecting to use different code for Table 3.

BTW, you will note that the example includes a few fields that I did not mention in my question, just to keep things simpler.

Are you saying I can simply create a query  and then export the query as a table?


SampleSLQtext.txt
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
I guess I wasn't clear in my description.  The queries in the above examples are select queries.  It is the TransferDatabase method that "makes" the table in the other database.  You could use tables instead if you wanted to export the entire table but queries are much more flexible since they allow you to specify only specific columns and use criteria to select only the rows you want.  In your case, you wanted to alter the column names and that is easily done with a query.

To Alias a field in QBE view, click into any column name and add the new name followed by a colon in front of the old name.  So, it will look like:
NewName:OldName
NewName becomes the alias for OldName and that will be the name you see in the table that is created.

To Alias a field in SQL view, add As NewName following the OldName.
Select ..., OldName As NewName, ....
0
 

Author Closing Comment

by:pcalabria
Comment Utility
Great solution.
Thanks!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 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

13 Experts available now in Live!

Get 1:1 Help Now