Solved

SQL help needed for MS ACCESS

Posted on 2013-11-20
4
454 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 36

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 36

Accepted Solution

by:
PatHartman earned 500 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

828 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