Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL help needed for MS ACCESS

Posted on 2013-11-20
4
Medium Priority
?
478 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 40

Expert Comment

by:PatHartman
ID: 39663153
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
ID: 39664555
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 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39665846
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
ID: 39671858
Great solution.
Thanks!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

927 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