Reports :: Making Sales Sheet On Access Using Existing Table

Aug 29, 2013

I have a table which has the fields: Agentname,SaleDate,Branch,Sales, Percentage, Comission, Corrections, rent and Total

Total = Sales-comission+Corrections+rent

This table has the name of the agent that made the sale, the branch(location) where he made it, the date, the commission of how much he gets from the sale, Corrections which is various correction that need to be made like a refund and or bonuses, Rent which he pays and the total.

what i want to do is to get access to make me a sheet( a sales invoice) where i can see the sales for a specific agent for a specific branch. agents can work at multiple branches.

so lets say agent A on branch A sold 400 on 1/8, 300 on 2/8 and 500 on 5/8 i want to get a list of all the days from 1/8 till 31/8 and the records of 1/8, 2/8 and 5/8 automatically attached to the correct dates and the rest of the dates should have a value of 0.

View Replies


ADVERTISEMENT

General :: Add Existing Fields And Property Sheet - Buttons Not Working

Sep 1, 2014

I have a problem with two buttons, add existing fields and property sheet

When i click them they do nothing at all, so i cannot do what i need to do?

View 9 Replies View Related

Making A Template Out Of Existing Database?

Apr 28, 2012

We have a shared personnel access database for our department. Another department is wanting to use the same database. Is there a way to go about deleting all the info and making a template to give the other dept to use?

View 5 Replies View Related

Reports :: Creating Reports From Imported Excel Sheet

Jul 9, 2013

i have an excel data for assets and i have imported it into access 2010 but i want to be able to do the ffg;

1. want to be able to generate reports like how many computers does a particular branch have.
2. i want to be able to sum the no of each field heading per branch

View 1 Replies View Related

Modules & VBA :: Store Data From Access Table To Excel Sheet In Corresponding Cells

Jan 28, 2015

I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .

Code:
Public Function Inputdata()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer

[code]...

View 2 Replies View Related

Modules & VBA :: Dynamically Create Headers In Excel Sheet Using Access Table Data

Apr 2, 2014

I have an access table and I want the code that will check two columns in the table "EnvelopeType" and "EnvelopeSize" and create headers in Excelsheet automatically. In the attached workbook, like in sheet1 the headers are already appeared, I want this to be done dynamically using vba code so that if new values get inserted in EnvelopeType and EnvelopeSize then we won't have to change the code to display more headers.

Please see attached workbook named Sample and Access table. E.g.

EnvelopeType EnvelopeSize
TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
TNT 2nd Class C5
PP1 2nd Class C5
Recorded A4
PPI 1st Class A4
Recorded C5

With the code it should display following headers in excel sheet:

TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
Recorded C5

View 14 Replies View Related

Modules & VBA :: Check If Values In Column A Of Excel Sheet Also Present In Access Table

Sep 2, 2014

In sheet 1, In column A all the Batch numbers are present. So I want to check if each of these is present in Access table named "tblmain". If its present then display its corresponding Policy number in Column B of attached workbook.

View 1 Replies View Related

Modules & VBA :: Copy Data From Excel Sheet To Access Table And Then Compare Two Tables

Dec 2, 2014

I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:

Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

[Code] .....

Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:

1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.

2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .

View 14 Replies View Related

How Can I Create A Sales Form That Can Update Both The Product And Sales Tables

Oct 11, 2007

Am creating a Product-Sales Database, and I would like the corresponding Sales made in the Sale Table to be automatically deducted or to be reflected in the Product Table. The product table contains all my stock and has a relationship with the Sales Table. The Sale Table does not necessarily include the Stock. How can I create possibly a Sales Form that will be used as an entry point for all the products (stock) sold and automatically register the sold products in the Sales Table and at the same time make the required adjustments in the Products Table.

View 1 Replies View Related

Reports :: Show Salespeople With 0 Sales Within Date Filter?

Jul 6, 2013

I want a very basic report that shows:* all field reps (sales people),

* their quota in terms of new customer registrations

* their actual new customer registrations for a particular month

The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.

Code:
SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations
FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep]
WHERE (((tbl_customers.dateRegistration)<#1/1/2013#))
GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;

View 2 Replies View Related

Modules & VBA :: Check Policy Number In Access Table And Populate Related Data In Excel Sheet

Aug 14, 2014

See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.

View 1 Replies View Related

Adding Columns To Existing Access 2003 Table

Jun 3, 2005

Hi,
I am fairly new to doing DBA, and I am having trouble adding a new column to a table that is existing. Is there any simple way to do this or does it have to be done throught code? And if so how.
Thanks
zorter8

View 1 Replies View Related

Can Map Certain Fields On New Access Table To Existing Access Table

May 14, 2013

I've imported an Excel file into Access as a textfile. All of the information on the Excel spreadsheet was successfully imported in Access. The only problem that I have is that some of the fields on the table have quotation marks (e.g. "Stults, David, "KWK, Inc.", "7,860.27", etc.) My first question is whether these quotation marks can be removed from the table.

My second question - There is information on this table that needs to be manually entered on a data entry screen in Access. Is there anyway to automate this process?

View 13 Replies View Related

Modules & VBA :: Export A Table From Access 2010 Into Existing Multiple Tab

Sep 25, 2014

I am trying to export a table from Access 2010 into an existing multiple tab excel 2010 spreadsheet.I want it to overwrite the "data staging" tab each time.I have it adding the tab into the existing spreadsheet but it names it "data_staging" however if I run this a second time I get excel found unreadable content in 'data staging' Do you want to recover the contents of this workbook? if you trust the source of this workbook click yes.

Code I am using

'export to existing spreadsheet data staging
Private Sub Command5_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Phx Data Staging", "F:My DocumentsWorkSGN est est data staging.xlsx", False, "data staging"
MsgBox ("Completed")

View 1 Replies View Related

Queries :: Making A Table From A Select Query In Access 2013

Jan 21, 2014

I have made a new access 2013 database. I have created a linked table that has imported a substantial amount of data from an external data source, (an Excel spreadsheet). So far no problem. I created a select query that plucked data from the original table mentioned. Again, no problem. Then I decided to create another table, using certain fields only from the select query. Microsoft's guide tells me to start with CREATE, then Table design. I am happy to use just 4 fields from my query, but what I keep ending up with is a table, that, when I double click on it gives me the following:

ID Field1 Field2 Click to add
(New)

It is presumably expecting me to enter an ID number and it will come up with some record, but I want a complete table that should show several hundred records.

View 1 Replies View Related

Import Excel Data Into Existing Access Table With Foreign Key Fields

Apr 25, 2015

I used to import excel data into access successfully, many times but now I have to import excel data into an existing Access table with foreign key fields, which makes me problems.

Its just doesn't work...and Im sure the forien key fields are the prob cause, the other fields are going well ...

View 1 Replies View Related

How To Populate Data Field With Specific Date To Existing Table In Access 2010

Jun 14, 2013

I have a table with more than a million records. I want to add a new column with date equals to e.g.3/14/2012 for all the records. I am using update query, but it is super slow (taking more than a hour)..is there any other way to do this?

View 6 Replies View Related

Modules & VBA :: Copy Data In Sheet1 Using Sheet 2 And Access Table Data?

Aug 5, 2014

I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.

The following formulas will be used in the select statment:

Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected

View 14 Replies View Related

Reports :: Attendance Sheet - How To Make Crosstab Query

Mar 15, 2013

I am making some attendance sheet on access as follows

Name !09:00!10:00!11:00!12:00!13:00!14:00!15:00
------!-----!------!-----!------!-----!------!
Test1 !Prsnt! ! ! ! ! !
test2 !Absnt! ! ! ! ! !
test3 !Vction! ! ! ! ! !

I have tables like

workersmaster(workerid,workername,joiningdate,stat us)
Workerdetail (Workerid,workername,entreetime,attendance)
timing(timing(0900to1500)

Now I wanted to make report as mentioned above, how can i do this?

View 4 Replies View Related

Making Reports

Jun 20, 2007

I need two reports made but I don't know the programming. The first report comes from a table that has contact numbers and their scan times. Their number comes from one column and their times come from another column in a table that i have. the time comes in this format 6/11/2007 3:46:40 AM. One report I need is those who just scaned in for that certain day. The second report is one to take their times and calculate how long they were scanned in per day, and per week. Is there any such way to do this and how would I.

View 12 Replies View Related

Setting Up A Table For Sales Figures

May 6, 2005

Dear All,
My boss wants me to create a budget report as follows:

Rep Cust ActM$ BudgetM$ VarM$ ActMProfit BudgetMProfit VarMProfit

ActYTD$ BudgetYTD$ VarYTD$ ActYTDProfit BudgetYTDProfit VarYTDProfit


I have a table with the following headings:

Date State Product Prod Code Customer Cust Code Category Jan Feb Mar etc

State has the domain Vic, NSW, Qld
Category has the domain Sales GP

Question 1:

To the field Category, should I either

A: add to the domain actual sales and actual profit? I can past these into the table at the end of each month.

OR

B: set up a separate table for the actual sales and actual profit for the month?

I think A.

Question 2:

Instead of having a separate column for each month, should I either:

A: just have a heading Month and put the figures in that column

OR

B: Have the sales figures in separate columns for each month?

I think A

Question 3:

When I have set up my table correctly, and assuming the answers to my questions above are all "A", I am now unsure how to create a query which will give me the data for the report my boss wants.


Can someone please give me some help?

Thanks
Bon

View 4 Replies View Related

Problems Making Reports

Feb 16, 2006

Hi!

It's me again, the beginner.

I'm trying to make reports of all my queries, but when I try to make reports a there's a message saying "enter parameter value". I don't know what to make of that.

I've made command buttons for some of the queries, maybe that's the reason.
When I for example try to make a report for the query "Kunder och telefonnummer", I don't see any names nor phone numbers on the report all I'm asked to do is enter parameter value.
My database is in the other thread"Delete query"
Please help me!

Nille

View 4 Replies View Related

Add New Field To Existing Table And Populate New Field With Existing Data

Apr 23, 2014

-Microsoft Access 2010

-Existing Access Database contains tables with 1-2 million records

I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].

Table1[main]
...account1
...account2
...account3
...dol

[code]...

In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.

Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].

View 14 Replies View Related

Extract Sales Data Based On Another Table

Nov 10, 2007

Hi All experts,

I got two tables while one table contains (sales data) and another one contains (criteria). I would like to extract sales data based on the criteria tables and export to a new table.

Which method is the best to complete this?

Criteria contains many lines like this

CustomerID, ProductID & InvoiceDt
A, Guliter, 2007/10/5-2007/11/7
B, Piano, 2006/7/1-2006/12/31

Thanks in advance!
Regards,
Stanwell

View 2 Replies View Related

Queries :: Table Of End Of Week Sales - Query Where There Is No Match

May 4, 2014

I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.

What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.

tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;

My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;

The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.

So that when I do year on year revenue calculations it works properly.

View 6 Replies View Related

Reports :: Adding Query To Existing Report?

Mar 15, 2014

Is it possible to add a query to an existing report? I use Access, not VBA.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved