SQL help needed for MS ACCESS
Posted on 2013-11-20
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.