Tables :: Create A Table Which Would Include Financial Data By Year / Client

Nov 15, 2012

I am trying to create a table which would include financial data, by year, by client. For example:

Client #1

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500

Client #2

2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.

Should I have headers as such:

Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome

View Replies


ADVERTISEMENT

Tables :: Create A Table With Entry For Month /day - No Year?

Jul 8, 2013

I'm trying to create a table with an entry for month/day, no year. I want to create a report that can will sort the birthdays based on month in ascending order.

I'm currently using the text field to put in, "10/30" or "1/3" but when I try to order the dates, instead of "1,2,3,4,5,6..." I get "1, 10, 11, 12, 2, 3.." etc.

View 11 Replies View Related

Creating A Financial Year Query

Nov 1, 2006

I am trying to create a make table query that extracts information from another table based on its year and month.

The problem I am having occurs when it comes to selecting data across multiple years ie. April 2005 - March 2006. The table that contains the data has two fields that are year and month.

I have created a form to allow the user to enter the required years, but I am unsure how to get the query not to select duplicate months e.g. April 2005 and April 2006.:confused:

Any help would be appreciated.:)

View 12 Replies View Related

Financial (Fiscal) Year Field

Jan 25, 2007

I have created a query in Access 2000 which contains a date field in short date format.

I would like to add a field to this query which calculates the financial (fiscal) year from my date field. Our financial year runs from Apr 1st to Mar 31st.

I'm totally stuck - any ideas would be greatly appreciated!!

View 3 Replies View Related

Queries :: DSum Not Working After December In Financial Year

Apr 17, 2015

I have the following in a query:

Count: Val(DSum("Num","Google_export_1Cancer_Target_Prea" ,"DatePart('m',[StudyEntryDate])<=" & [AMonth] & " And DatePart('yyyy',[StudyEntryDate])<=" & [AYear]))

Our Financial Year runs from April to March.

The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.

How do I rectify this?

View 4 Replies View Related

Create A Form Which Allows To Enter Client Data

Mar 4, 2012

I'm creating a Dating Database on MS access, Now I've been given the attributes for all the tables. They are five (Client, Hobby, Meeting, ClientHobby, ClientMeeting)

Now I'm needed to create a form which allows me to enter the client data + at least two interests and their age calculated from their DOB. Seeing as I was only asked to put DOB in the actual Client table, I thought I'd create an Age query and then make that form out of the Age Query. (I already successfully managed to get the Age out of DOB in that query in a field of its own)

However, how can I add the hobbies field to that age query so I can finally create the form that I am required to do?

Second, I am to create a query which will be used for ‘matching’ clients. The criteria that you will use are: gender, age (using a range e.g. 20-25), interests/hobbies and city/town, this query ill be used to create a report which lists all the clients who match the entered criteria. How am I to do that?

View 4 Replies View Related

Queries :: Group Financial Data To One Main Table - Round To 2 Decimal Places

Feb 25, 2014

I have about ten append queries to group various financial data to one main table.

I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.

What is the better approach to have only 2 decimal places for all append data?

View 4 Replies View Related

Criteria To Include Partial Year(Now())?

Apr 25, 2007

I have a table with a field QuotationRef containing a series of values which include a year indicator (eg Q11101/06, Q11102/06, Q11103/06, Q11105/07, Q11106/07, Q11108/07) - where the characters after the / denote the year a Quotation was started. I need to be able to setup a criteria filter expression in my query design view that will allow me to select just the values in the QuotationRef field that relate to the current year.
I can set up a query that picks up this year's Quotations by using "Like "*/07"" but that will only solve the problem for this year and I need to setup a query that will change when the year changes. How could I do this, I have tried incorporating "Year(Now())" in the expression but this would filter on /2007 rather than /07.
Any advice please?

View 3 Replies View Related

Queries :: How To Create A Query Which Gives Data From Previous Year

Mar 4, 2014

I am trying to create a query which will return records which are from a specific month of the previous year...

To better explain:

If I have sales from 2013 and 2014 in Jan, I want a query which will show me the results from Jan in 2013 only... I'm just unsure on how to make a query do this and it's probably really simple.

But then in 2015 I will want it to show results from 2014 without having to change the expression or criteria.

View 4 Replies View Related

Tables :: Relationship - Client Data Is Not Populating Into Routing Form

May 20, 2015

I have created 2 tables: Client and Routing.

Each client record is unique. A client can have 'several' routing records.

Key field in Client is 'Client ID'.

Routing table has foreign key of: Client IDFK

I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.

I created a form for the Client table and works.

Where I am having issue is: Client data is not populating into the following Routing form.

* I want a 'Routing form' that you can lookup client info and place it into that form.
* The bottom of the form will be all the routing table fields. The new routing info will be entered into it.

My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.

View 3 Replies View Related

Tables :: Change Client ID Data Type From Text To Number

Mar 27, 2014

I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".

I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?

Client Table:

Client ID(Autonumber)
Client Name (Text)
Client Address (Text)

Order Table:

Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price

View 3 Replies View Related

General :: Create List Of Month / Year Between Data Range

Aug 19, 2013

I have a list of customers with multiple rent start and end dates. I need to create a query where each customer will have a record for every month/year between their respective rent start and end dates. I generally use macros but can't figure out a way to do it without VBA. My field names are Rent StDt and Rent EndDt. My query name is QCalcREndDt. I use version 2010.

View 6 Replies View Related

Tables :: Check If Client Name Exists In Table?

May 28, 2013

Using VBA (in Access), what code should I use to check if a client name already exists in a table.

Say, client name is "Smith", my table is called tblClient and the field name in the table is called fldClientName.

I just need to do the check, that's all.

View 3 Replies View Related

Extract Data From Table On URL (to Include PNG Files)

Oct 25, 2013

I am a new user to access. I would like to extract a table from a URL, it is 4 fields and has around 150 records. One of the fields is made up of png files, relatively small ones. Is it possible to extract all the text and png files straight into access without individually saving each picture and attaching it to a record?

View 2 Replies View Related

Tracking 250 Pieces Of Data Per Client. Table Structure Help Needed.

Mar 12, 2006

hi everybody,
great resource you have here! my employer is tracking around 250 pieces of data for each client at our facility. i am making a new access system based on their existing mysql database and web front end. before i start messing around with forms and reports, i want to see how well this existing structure will work in access, and what kind of approach i should take. i am a newbie with access, but lots of experience with asp/mssql/php/mysql. making web forms is so time consuming that i figured i would be best off moving the whole thing to access and starting from scratch.

client information is stored in eight tables. each table has around 30 fields in it. the first table has a primary key autonumber, and the other seven tables have foreign keys with unique constraints that point back to the first table. that is, for each client record in the first table, there can only be exactly one corresponding record in the other tables.

i did some data massaging, and got the eight client tables into one big table, but the resultant table has almost 250 fields in it, and access doesn't seem to like working with tables that big. so i am thinking that it is best to leave the eight tables separate, but linked in one to one relationships.

i was kind of ideally visualizing a form with eight tabs so that i could edit/update all of the information from the eight tables rather seamlessly.

my question is: what approach to table structure will best suit my needs, and what approach should i take to add/update/delete the info with forms? will i need to do vb for this? any good one-to-one example databases anybody could point me at?

thanks a million,
harry doyle

View 1 Replies View Related

Tables :: Record Cannot Be Deleted Or Changed Because Table Include Related Records

Oct 16, 2012

Three tables:

Employee, Sessions, EmployeeSessions.

Many sessions can have many employees - thus the joining table has been included.

When trying to delete an employee from the database using a form, I encounter the error:

The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records

Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?

View 2 Replies View Related

Queries :: Table With Duplicate Records - Return Distinct Data For Each Client

Jan 17, 2014

I have a table with duplicate records (which is ok) and I want to return distinct data for each client.

It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.

I.E

SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient

Works fine with only the Distinct records for each client returned

However

SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient

Now returns Duplicates!

Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?

View 5 Replies View Related

Modules & VBA :: Selecting Fields - Change Text To Include Data From A Table

Nov 5, 2014

I have an on click event to mail a report which works. I want to change the text to include data from a table.

I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.

Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub

View 1 Replies View Related

Forms :: Include Option In Combo Box To Create A New Item

Jun 20, 2013

I have a form with a combobox whose list items are taken from a table of currencies (called tblCurrencies)

Row Source = SELECT [tblCurrencies].[Ccy] FROM tblCurrencies ORDER BY [Ccy];

Problem is, there may come a time when the currency they want to select is not yet part of the tblCurrencies table, and needs to be added. For simplicity, I'd like to have an additional option in the dropdown (perhaps at the bottom) to specify a new currency.So an additional list item (e.g. 'Add New...') which when selected could prompt a subform where the user could specify the new currency (and any other relevant details specific to that currency) which can be programmatically added to tblCurrencies.how do I get that extra option into the list items for the combobox?...

View 2 Replies View Related

Tables :: DCount - Calculate ID For Month And Year From Another Table

Sep 25, 2014

I have a table that displays the year and month and another field that I want to calculate how many id have that month and year from another table.

View 3 Replies View Related

Current Client/Prospective Client Database

Nov 9, 2005

Hi all

Brand new on here and desparate for some help and guidence.

So far with Access I have just used it as a store of addresses to mailshot prospective clients.

However, I now need a more complex database and this is where you might be able to help.

First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.

Does this mean I need to do three tables:

1/ "University Details" which gives the address details
2/ "Department" storing the departments of the unis we work for
3/ "Client" Name of the client(s) in that department.

After this how do I link them?

Thanks

Razieluk

View 4 Replies View Related

How To Create Reset A Autonumber After Every Year

Feb 18, 2007

Hi I am trying to make a database, In which I have a table linked with the form.
There are two fields in the table 1.Serial Number & 2. Current Year
I want the serial No. field to be incremented after every record is added & Also the numer should start from "1" again as the Current Year Changes.
Can somebody help me in this.
I am learning new things in access & not that proficient. But i love to work in access.

View 11 Replies View Related

Create A Fiscal Year Expression

Jul 9, 2006

I'm trying to change the way date calculations are done in a training db. I show them now in an expression in my query as Between Date() And Date()-365, but I need to show them based on a FY beginning 7/1 of any year and ending on 6/30 of any year. Any suggestions?

View 1 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Include Permissions When Transferring Tables

Jan 15, 2007

I am using DoCmd.TransferDatabase to transfer 2 tables from one database to another. This works fine, however, the user permissions for those tables do not carry over with the table and I have to go into the database and set them as they were in the original database.

Simply, I want to allow complete access for all users to the tables in the second database.

Does anyone have a solution to this?

Thanks

View 2 Replies View Related







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