How to have "Quantity" in a table keep adding every week.

[dbruzzese84]

I have been trying to work on this project but stumble across a few mistakes. Basically, I have a search form with 2 listboxes. When the user chooses both options and presses search, a table shows those specific entries searched for.

The problem: Now, every week a new table is inputed because of new data to the company. I need to know how I can have "Quanity" from previous weeks keep ADDING ON to the new weeks table. Is this possible?

[MSeda]

It would be best to append the new data to the existing table rather than create an new table every week. You can import the weekly batch table to a temporary table and then run an append query to add it to the main table.

[dbruzzese84]

That makes sense :) I am sort of new to access and learning as i go along. How would I go about doing so?

[dbruzzese84]

can anyone help me please?

[MSeda]

Sorry for taking so long to get back.

what you want to do is have a master table that has the fields that appear in the spreadsheets you'll be importing weekly.
Then you will need to create an append query that appends the data from the temporary data you are importing to the master table.
Once you have this set up just have the user import the weekly table always saving to the temporary table and then run the query that appends the data to the master table.
to give more details on how on how you want the forms that perform the operation to look and function and where the data is imported from (excel, another database?)

[dbruzzese84]

Sorry for taking so long to get back.

what you want to do is have a master table that has the fields that appear in the spreadsheets you'll be importing weekly.
Then you will need to create an append query that appends the data from the temporary data you are importing to the master table.
Once you have this set up just have the user import the weekly table always saving to the temporary table and then run the query that appends the data to the master table.
to give more details on how on how you want the forms that perform the operation to look and function and where the data is imported from (excel, another database?)

the data is imported from an excel spreadsheet. so far i got the form to search for what is selected in both listboxes. here is my code:

SELECT SalesLic952.CSPC, SalesLic952.DESCRIPTION, SalesLic952.QTY, SalesLic952.NAME, LSList.CITY, LSList.ADDRESS, LSList.[PHONE NUMBER], LSList.[POSTAL CODE]
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO=SalesLic952.CUSTOMER
WHERE SalesLic952.CSPC=nz(forms!Form1!List1,"SalesLic952 .CSPC") And LSList.City=nz(forms!Form1!List2,"[LSList].[City]")
ORDER BY SalesLic952.CSPC, LSList.CITY;

Where in here do I add information about master and temporary tables?
I had a lot of help with this code. I am not really that familiar with it, but I do understand it and what they mean. Do you think you can type a sample code of what ur talking about above?

[dbruzzese84]

the data is imported from an excel spreadsheet. so far i got the form to search for what is selected in both listboxes. here is my code:

SELECT SalesLic952.CSPC, SalesLic952.DESCRIPTION, SalesLic952.QTY, SalesLic952.NAME, LSList.CITY, LSList.ADDRESS, LSList.[PHONE NUMBER], LSList.[POSTAL CODE]
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO=SalesLic952.CUSTOMER
WHERE SalesLic952.CSPC=nz(forms!Form1!List1,"SalesLic952 .CSPC") And LSList.City=nz(forms!Form1!List2,"[LSList].[City]")
ORDER BY SalesLic952.CSPC, LSList.CITY;

Where in here do I add information about master and temporary tables?
I had a lot of help with this code. I am not really that familiar with it, but I do understand it and what they mean. Do you think you can type a sample code of what ur talking about above?

basically, i just want to have the quantity in numbers changed to what this weeks sales volume is, without losing numbers that havent changed as well.

[MSeda]

The searching of the data and the additions of weekly records are two separate issues, I am addressing the issue of importing the weekly additions, the query you have for your search form looks fine but is irrelevant to the issue of how to handle the importation of a weekly excel spreadsheet.
To handle the importation of the spreadsheet it is probably best to use a separate form designed specifically to assist the user in updating the DB with this weeks info.

You will need to design an append query to append the data from your weekly import table to the master table. just use the access query designer. select the weekly import table and when you change the query type to append it will ask what table you would like to append to, Choose the master table. append all of the feilds to the similar fields in the master table.
On the importing form you need to have a way for the user to decide which spreadsheet to import (unless the spreadsheet is always the same name and path). This can be as simple as a textbox where they type the file name or you could use something like a common dialog where they can browse for a file.
in addition to the selection control you also need a command button the on click event for your command button should look something like this (more or less)

Private Sub Command5_Click()
Dim mySheet As String
 
mySheet = ????? 'reference the control the user is to select the excel file in
 
'Import The spread sheet to weekly import table when the table is imported it will overwrite the weekly import table with the new spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Weekly Import Table", mySheet, False
 
'Run the query that appends the data to the master table
DoCmd.OpenQuery "Add Weekly Data"
 
msgbox "Data Import Complete"
End Sub
 

once the data has been added to the master table (the one you want to query on the search form) then it will be available on the search form.

[dbruzzese84]

this works thank you. the only issue im having now is that the entries are not doubling up on each other. new entries in the table are being made and this is making duplicate store names. For example if Store#3 ordered 1 last week and 1 this week, the new imported table should state QTY:2. Instead it has Store #3 QTY:1, Store #3 QTY:1. How do i fix this to not show a duplicate?

[dbruzzese84]

The searching of the data and the additions of weekly records are two separate issues, I am addressing the issue of importing the weekly additions, the query you have for your search form looks fine but is irrelevant to the issue of how to handle the importation of a weekly excel spreadsheet.
To handle the importation of the spreadsheet it is probably best to use a separate form designed specifically to assist the user in updating the DB with this weeks info.

You will need to design an append query to append the data from your weekly import table to the master table. just use the access query designer. select the weekly import table and when you change the query type to append it will ask what table you would like to append to, Choose the master table. append all of the feilds to the similar fields in the master table.
On the importing form you need to have a way for the user to decide which spreadsheet to import (unless the spreadsheet is always the same name and path). This can be as simple as a textbox where they type the file name or you could use something like a common dialog where they can browse for a file.
in addition to the selection control you also need a command button the on click event for your command button should look something like this (more or less)

Private Sub Command5_Click()
Dim mySheet As String
 
mySheet = ????? 'reference the control the user is to select the excel file in
 
'Import The spread sheet to weekly import table when the table is imported it will overwrite the weekly import table with the new spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Weekly Import Table", mySheet, False
 
'Run the query that appends the data to the master table
DoCmd.OpenQuery "Add Weekly Data"
 
msgbox "Data Import Complete"
End Sub
 

once the data has been added to the master table (the one you want to query on the search form) then it will be available on the search form.

mySheet = ????? 'reference the control the user is to select the excel file in

what does this mean? what do i put in the ?????

[dbruzzese84]

mySheet = ????? 'reference the control the user is to select the excel file in

what does this mean? what do i put in the ?????

does this overwrite the previous data? i need to add on to it without duplicating records.