Can I Add A Column To A Table And Give It A Name Using VBA?

Feb 22, 2008

Hi all

I am currently building a holiday planner for my employers and I would like to try and display the days of the year as rows and the staff as columns (Fields). When someone adds a new member of staff, I would like it to also add them to the calendar table as a new column and name the column the same as the individuals name. Is this possible using VBA or SQL string?

If anyone has a better idea as to how I can display the forthcoming holidays for a team I would really appreciate your comments.

Thanks

Gareth

View Replies


ADVERTISEMENT

How To Link Table And Give Reference Of Column

Sep 10, 2015

I have created two tables, I want to copy one column from one table to another table column with repeat value in rows.

example :- copy "Material number" column from Table1 to Table 2. In Table 2 Material number will repeat in 20 rows.
Like in excel we give one cell reference to multiple rows.

How I can link table and give reference of column.

View 1 Replies View Related

Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column

May 21, 2015

I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).

View 3 Replies View Related

General :: How To Have Supplementary Auto Numbering Column To Give Invoice Numbers

Dec 29, 2014

I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.

View 8 Replies View Related

Please Give Me Feedback On My Table Structure, Will It Work?

May 2, 2007

Hello dear experts!
Will this table structure work? - Please help!
I need to be able to see if certain members have paid subscription, see if stakeholders have paid for their room bookings, etc. Will it work, does it look ok to you?

Im trying to brush up my normalisation skills and logic.

Please click the link below to see my ER-D:

17320

Thanks, Very much, Ant...

View 8 Replies View Related

Populating A Column In A Table Based On Values In An Existing Column

Mar 5, 2007

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc


I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.

So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc

How do I do this?

Kind Regards,

Matthew

View 1 Replies View Related

Copy Data From A Column In One Table To A Column Another.

Jul 18, 2007

Please would you be able to advise me how I would copy data from a column in one table to a column another table.

But I need to only use table not a query.

Thank you in advance for your help.

View 10 Replies View Related

Queries :: Update In Table From Same Table Based On Criteria Column In Same Table

Sep 29, 2013

How can I update (some columns) in a table from the same table based on a Criteria column in the same table.

View 2 Replies View Related

Please Help - I Give Up!!!

Dec 21, 2006

I couldn't find a similar question already answered and I am stuck at home due to the blizzard here in Denver Colorado so I can't get any other resources to help. I am working on a select query where I am using two table's and another select query to pull information.

query HOLDINGS HASH QUERY #3:
- AS_OF_DATE
- SAP
- ACCNTNUM
- SumOfSumOfAS_OF_BOOK_VALUE_4TH

table HOLDINGS HASH CGAAP ADJUSTMENT TABLE:
- BEGIN DATE
- END DATE
- SAP
- ACCNTNUM
- SUM VALUE

table SAP DETAIL TABLE:
- Fiscal Year
- Company Code
- Account Number
- SumofAmt CoCd currency
- Cabinet

Currently the join properties include: from SAP DETAIL TABLE the Company Code and Account Number to the HOLDINGS HASH QUERY #3 SAP and ACCNTNUM, from HOLDINGS HASH QUERY #3 SAP and ACCNTNUM to the HOLDINGS HASH CGAAP ADJUSTMENT TABLE SAP and ACCNTNUM.

There are three things that I am trying to accomplish with this query. The first is pulling down the SAP and ACCNTNUM fields which works fine. The second is pulling down the SumOfAmt CoCd currency and SumOfSumOfAS_OF_BOOK_VALUE_4TH which matches the SAP and ACCNTNUM fields which works fine.

What I'm trying to do for the third is to only pull the SUM VALUE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE that matches the SAP and ACCNTNUM fields and also where the AS_OF_DATE from the HOLDINGS HASH QUERY #3 is between the BEGIN DATE and END DATE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE.

This query will then be the output for a report.

In order to accomplish the third task I have tried to complete a SELECT statement and a IIF statement that have not worked. I am sure the select statement would work but I just can't figure it out. Any help would be very very much appreciated.

Sam

View 2 Replies View Related

Trying To Give Each Occurance An ID

Sep 6, 2006

Hi Guys, Ive been struggling to create a query, I wonder if anyone knew how to do this.

I have data setup basically like this: -

Code Customer
A CustA
A CustB
B CustC
C CustD
D CustH
D CustI
E CustE
F CustF
G CustG

Is there a way i can make a count work to show like this: -

Code ID Customer
A 1 CustA
A 2 CustB
B 1 CustC
C 1 CustD
D 1 CustH
D 2 CustI
E 1 CustE
F 1 CustF
G 1 CustG

So if there is more than one customer, the ID will accumulate until the code changes so it will revert back to one again.

Hope someone knows how to do this.

Thanks

Predator002

View 2 Replies View Related

About Ready To Give Up On Access

May 12, 2005

I work at a Phamaceutical company in the US (based in London) and have a database that tracks atypical events. It has normally about 10 concurrent users and continually (once a week sometimes more sometimes less) corrupts. I have spent a lot of time validating the database writing the user requirements, functioanal and design specs as well as a 150 page IOPQ.

It is a split database with both front and back ends on a file server. I know this is not the best way to do it but the database is frequently updated with requests for new reports etc and I am not sure how to push down the front ends to all the use pcs. The database is about 40 mb.

I like the database. It is fully audit trailed and does what I need it to do, but the corruption thing is driving me batty. I have the jetutils with the viewer to see who is in the db (by pc name) and then I cross reference that to the person in the db and call them to get out. It is a real pain in the arse and I am just about ready to agree with the IT guys that Access is not a multi user database.

Should I scrap Access and go to SQL Server?

View 7 Replies View Related

How To Give A Commit Statement In VBA?

Mar 6, 2007

Hi,

Do i have to give a commit statement when i update tables in backend(insert, delete,update) from a form, say when user clicks submit, it takes the data in fields and inserts it into the table , but till now i never did that but i could see all data when i queried the table, but i am not sure if that would be right, if i have to give Commit, how do i do that using VBA?

Any help would be appreciated!

Thanks so much in advance!

View 2 Replies View Related

Copy A Query And Give New Name

Mar 11, 2015

I have Access 2010 and I have a query that I want to take a copy of, save as and call it call it something else in the same database.

For example "query1" I want to save as and call it "query2" .

View 7 Replies View Related

Modules & VBA :: Update Column In Table Based On Another Table

Apr 24, 2015

I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.

I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"

The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.

View 1 Replies View Related

Diff Expresions Should Give Same Result But Don't!

Sep 22, 2006

I've got a table in which there are 20 numeric columns. For one report, I want to add all of the data in those columns together. I used the expression:

Seats: Sum(DailyActivity!PkgSeq01Cnt+DailyActivity!PkgSeq 02Cnt+DailyActivity!PkgSeq03Cnt+DailyActivity!PkgS eq04Cnt+DailyActivity!PkgSeq05Cnt+DailyActivity!Pk gSeq06Cnt+DailyActivity!PkgSeq07Cnt+DailyActivity! PkgSeq08Cnt+DailyActivity!PkgSeq09Cnt+DailyActivit y!PkgSeq10Cnt+DailyActivity!PkgSeq11Cnt+DailyActiv ity!PkgSeq12Cnt+DailyActivity!PkgSeq13Cnt+DailyAct ivity!PkgSeq14Cnt+DailyActivity!PkgSeq15Cnt+DailyA ctivity!PkgSeq16Cnt+DailyActivity!PkgSeq17Cnt+Dail yActivity!PkgSeq18Cnt+DailyActivity!PkgSeq19Cnt+Da ilyActivity!PkgSeq20Cnt)

Where dailyactivity is the name of the table, and pkgseq01cnt is the name of the first column, etc.

The expression returns the number 961.

If i make a query where i sum all of the columns individually, and then a query based off of that query where i add all of the sumed columns together, i get the number 965. (which, as far as i can tell, is the right number)
the expression in the second of those querries is:

Seats: Sum([seat]![SumOfPkgSeq01Cnt]+[seat]![SumOfPkgSeq02Cnt]+[seat]![SumOfPkgSeq03Cnt]+[seat]![SumOfPkgSeq04Cnt]+[seat]![SumOfPkgSeq05Cnt]+[seat]![SumOfPkgSeq06Cnt]+[seat]![SumOfPkgSeq07Cnt]+[seat]![SumOfPkgSeq08Cnt]+[seat]![SumOfPkgSeq09Cnt]+[seat]![SumOfPkgSeq10Cnt]+[seat]![SumOfPkgSeq11Cnt]+[seat]![SumOfPkgSeq12Cnt]+[seat]![SumOfPkgSeq13Cnt]+[seat]![SumOfPkgSeq14Cnt]+[seat]![SumOfPkgSeq15Cnt]+[seat]![SumOfPkgSeq16Cnt]+[seat]![SumOfPkgSeq17Cnt]+[seat]![SumOfPkgSeq18Cnt]+[seat]![SumOfPkgSeq19Cnt]+[seat]![SumOfPkgSeq20Cnt])

where 'seat' is the name of the first query that sums the columns.

I think those two ways should both come up with the same number - but i can't figure out why the first way is missing four! there are thousands of rows so it is hard to go through the table itself. Also, in both of those ways, the querry includes a 'where' column which limits it to rows where another column equals a certain word - but that's relatively straight forward - dont' know why that would make it different.

any ideas?

Thanks! It's driving me crazy!

View 2 Replies View Related

Making Query Give Me Anything For Dates BETWEEN 10/01 And 10/15

Nov 10, 2006

Hi!

I'm trying to write a query that will display my data for anything that has a date between 10/01 and 10/15. I went into the "Build" area and looked at the "functions" available but none of them sound right - and the one book I have on Access is not so great.

Can someone help me with the correct formula?

Thanks!

View 2 Replies View Related

Can Someone Give Me Some High Level 'pointers' ?

Oct 25, 2006

Can someone give me some high level 'pointers' ?

Simply (although not for me) I am trying to build a form that involves a unique identifying number being entered, a button being pressed and relevant field belonging to that unique identifying number being displayed in a subform.

For example, I might want to enter a customer number, press a button and all the orders made by that customer appear in a subform.

Any help gratefully received !

Thanks.

View 3 Replies View Related

Subdatasheet Not Sufficient, Please Give Alternative

Dec 28, 2004

What I am doing, is I have various products with 5 basic fields. I then wanted to add an array of text fields to 2 of those original 5 basic fields. I attempted to do this with a subdatasheet, however, a subdatasheet is not record specific, is not directed to a specific field, and really does not do anything that I want.

Say that the basic field is ProductDescription. I want one column of fields to correlate to it, like below.
ProductDescription
Is blue
Is large
Is heavy
Is strong

Each product will have a different amount of varying descriptor fields. Once understand how to incorporate this with my project, I then will need to understand how to add the subdatasheet to a report easily. Thanks for any help,
-SethTherrien

View 3 Replies View Related

General :: No More Records Available To Give Message

Dec 11, 2013

I have a table that is a list of available UPC codes that through forms are being assigned to several different tables (types of items). When the UPC code is assigned, there is a yes/no box which is checked. I need to have a message box appear when the last of these UPC codes has been used (or better yet perhaps when there are a specific number left - such as 10 - so the user isn't all of a sudden locked out of any more work).

It could even be triggered when a specific UPC code is reached - they will be in numerical order (ignoring the check digit). Is there anyway to do this globally or would the check need to be on each form as the UPC is assigned?

View 6 Replies View Related

Adding 2 Fields To Give The Default Of 3rd Field

Jun 15, 2006

Hi people,

May I know how to add 2 fields.

i.e
My table has 3 columns

Field1 Field2 Field3

The value of field3 = field2 + field1 (the formate of field1, field2 n field3 are currency)

how do I indicate that the value of field3 is depended on the value of field2 n field1

is it under the default value of field3(in the TABLE design view)that i indicate field2 + field1?

thank u

View 1 Replies View Related

Counting With A Query To Give Results On Report

Dec 18, 2007

I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.

Can someone help me with this?

- Thanks in advance!

View 10 Replies View Related

Query On Form. (combo Box To Give Criterea)

Mar 22, 2006

I have changed my approach for my database, and i think a query is what i needed all along. But i am not sure how to go about it.

Is it possible to have query results on a form?

more precise, i have a table full products, alot of information per product (about 30-50 fields)

The amount of actual products isn't many probably around 10, and it's likely to stay at around 10 products.

Can i have a drop down list listing all the products, so it looks these up from the product table, you select the product, that product field value becomes the criterea for the query, and all the associated form fields get filled out to do with the product selected.

To summarise, can i have this all done in a form? so it's easy for staff to simple go the to product drop down list, select a product, all the info relating to the product is then displayed on the form.

hopefully i have made sense with my post.

View 10 Replies View Related

Forms :: Non Matching Records To Give Message Box?

Dec 11, 2013

I have two tables, categories and items. I have a form that is linked to the categories table and a subform that is linked to the items table. The user selects a category on the main form then an item to add to the category on the subform. (all of this is functioning correctly - but you need to understand for my question.)

Both the form and the subform have a text box that displays a record from their respective table, for the example we will call this color.

What I need to do is create code that checks the color text box from the category (main form) against the text box for the item (subform) and if they are not a match I need to display a message box - from where I can then add the necessary actions.

EDIT: note this is not a query on an entire table or 2 it is just comparing the two text boxes on the form and subform

View 5 Replies View Related

Forms :: Can't Give Conditions On Validation Rule?

Jun 1, 2015

Can't we give conditions on Validation Rules? (Like IIf)

IIf(getusername()=[Log_Resource],Between DateAdd("d",-1,Date()) And Date(),Between DateAdd("d",-2,Date()) And Date())

View 8 Replies View Related

Reports :: Give Control A Unique Criteria

Apr 15, 2015

I have a report that is filtered like Year_ = "2014/15".

Say for example on this report it contains two text boxes:

Text box 1 has a control source of "March_T"
Text box 2 has a control source of "Aprril_T"

How can I make text box 1 be filtered by Year_ = "2014/15" and text box 2 be filtered by Year_ = "2015/16"?

View 2 Replies View Related

Forms :: How To Give User Authority On One Button

Apr 27, 2014

how to give someone authority on one button in form

View 8 Replies View Related







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