Say I have a table/qry:
ID | VARIETY | PRODUCT
1 | Fresh White | Garlic
2 | Fres Valencianita | Onion
Is therey a way to merge variety and product? Cause I want to make an invoice description with a text that can be made out merging fields of a same table/qry.
Sth like the following:
"[QUANTITY] of [VARIETY] [PRODUCT] from [COUNTRY], size: [SIZE]...."
I saw a topic about listing items horizontally but I think it was kinda different from what I wanted, specially cause involved more than one table.
basically I have two date columns in one of my tables. One is created whenever a record is created in that table the other is added to that record later based on business rules. I need to get all records in the table where the first date exists but not the second. I'm unsure of how to write a query specifying that the second date column must be empty.
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this: Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Would you be able to have the same databasing power in vb.
a company offered me a job but they would prefer if all my programs where in vb, but i have never even lokked into it. i know there are a few extra thing like menus and stuff like that.
Would i be able to say that since i know vba i would be a ok candited?
I have a query that creates 8 columns, exactly as I want. However, the information in the last 2 columns, Dbk and Com, I'm trying to get on the same row. Right now, when the query is run, the Dbk amount shows up on one row and the Com column is blank, then the next row shows the Com amount and the Dbk column is blank. Is there a way to get both values to show on the same row? Thanks.
SELECT tblCorpCodes.[Client Code], tblCorpCodes.[Client Name], tblCorpDollars.[Month/Year], tblCorpDollars.[Invoice #], tblCorpDollars.[Drawback Entry #], tblCorpDollars.[Claim Ref #], IIf([tblCorpDollars]![Income Type]="Drawback",[Amount]) AS Dbk, IIf([tblCorpDollars]![Income Type]="Commission",[Amount]) AS Com FROM Query4, tblCorpCodes INNER JOIN tblCorpDollars ON tblCorpCodes.[Client Code] = tblCorpDollars.[Client Code] GROUP BY tblCorpCodes.[Client Code], tblCorpCodes.[Client Name], tblCorpDollars.[Month/Year], tblCorpDollars.[Invoice #], tblCorpDollars.[Drawback Entry #], tblCorpDollars.[Claim Ref #], IIf([tblCorpDollars]![Income Type]="Drawback",[Amount]), IIf([tblCorpDollars]![Income Type]="Commission",[Amount]) HAVING (((tblCorpCodes.[Client Code]) Like "566") AND ((tblCorpDollars.[Invoice #]) Like "7315"));
I am new to this forum, so forgive me if this question has been asked before
I needed exact information about the way the 'like' operator works, so I pressed F1, typed 'like' in the "Answer Wizard" and was told to "rephrase my question". :eek:
The index was slightly more helpful as it obviously contains 2 topics with 'like_operator', but choosing them does not reveal anything. :confused:
Where can I find info about such things? I also need to use an operator called 'contains' which I have used when working with SAS, but appearently it does not exist in Acccess or it has another name. Has anybody heard about it?
I have 3 tables: tbl_1, tbl_2, tbl_3 All 3 have the same 3 columns: name, email, code
Can I write some queries or a code or something that can create one "tbl_MASTER" with the same 3 columns?
Ex: tbl_1 - 3 records tbl_2 - 7 records tbl_3 - 21 records
tbl_MASTER - 31 records. Now the information in the other 3 tables changes everyday so I want it to all be automatic, I dont want to execute 3 different queries or anything like that. Is this doable. Thanks.
Can someone point me in a direction to get some good information on how and when to use recordsets? Ive gotten by without having to use them for the most part up until now.. but the inevitable has happened and i figure i might as well learn to use them
for the most part i know When to use them, but its getting into the how and where to use it for the most part.
simple examples or even perhaps a tutorial would be great! thanks guys
Guys i have tried to seach the Forum but can't find a solution to my need.
I would like to get a collection of table names from another DB, what i am trying to do is populate a table with a list of table names that the front end links to and their paths once a user has selected the data file location.
Instead of me having to force a user to place the DB files in a location of my choice when the database first loads it will prompt the user to select the location of the data file then (this is why i need the above) populate a table with a list of table names and their source, then establish a link to them for the front end to work.
Also if i ever send amended DB files or they move the location of the data files they can automatically re-establish the linked tables.
I have been struggling with a project and cannot seem to get a solution. The following is a bit drawn out so I apologies, but if anyone can help - (or tell me if it is not possible) I would be grateful.
I have a club DB with 42 members, and have a table with members details etc.
They meet 12 times a year and I have a table with details of the meetings (each meeting is a separate record with date, venue etc)
I use forms to view the records.
What I would like to do is be able run a query/report to see who attended each meeting/or how many meetings an individual attended over the year. (I have not got to the query/report stage yet, as I cannot figure out how to set up the information).
(I need to know each year how many meetings an individual attends)
I thought of putting a list of the members and a tick box next to their name on each of the 12 meeting records, (which will update when members join/leave) then be able to run a query with the name of the member, and the dates (or number) of the meetings attended, but don'y know how.
At the moment all I can think of is a memo field, and just type in their names on a specific meeting record.
I have a form with some controls, and for some of the controls, I would like to provide some info, when a user key presses a particular key. I would like that when this assigned key is pressed a small box will pop up providing the relevant information. However, for this feature i do not wish to use the msgbox, to provide the information in, but i would like to use something as those "white boxes" which are normally used in help files (the ones that normally describes a definition of a particular term).
Can this be done in access, and if you kindly could you point me in the right direction.
Suppose I have sharing drive, let's say "\server1sharefolderDatabasesdb1.mdb"
if I run this CurrentProject.Path inside the database file, it will retreive the current path with drive letter, but how can I retreive the server, and sharefolder information?
Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum. I am having problems with a log in box for a database, the line of code is - If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.
Questions - A) It is not looking up the value for [customerID] - what have I done wrong?
B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?
C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?
It seems that there are a few different ways to get info from the tables when developing an applications that i know of:
1)queries 2)dao 3)ado 4)selects without any of the above
I lean towards the last 2 but I need to know this: is there any advantages in using ado as opposed just sql?
For instance i ran this sql:
Private Sub Command0_Click()
Dim SQL As String Dim strCriteria As String
strCriteria = Forms![form2]![Text1]
SQL = "SELECT * FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"
DoCmd.OpenForm "frmClinic" Forms![frmClinic].RecordSource = SQL End Sub
it worked fine. I could see how a beginner would prefer to use queries over sql but I would prefer to use sql in vba. I've read that dao is older and I should use ado instead. So that leaves ado vs. the way I displayed it in the above code. So which way should I go? If I'm over looking queries or dao in favour of ado/sql, just point out their advantages if you don't mind.
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
Can anyone tell me a sample code on how to write/develope the Advance file properties like....
File version Description Internal Name Product name
I have seen different types of files properties some has additional features while some has none - its just click once on any file and then click the right hand side mouse button and select the properties that will display all the information about the files click on second tab version (if there) will tell you the additional info about the file.
How would I get Access to autopopulate a table's text field with the user's computer name when they enter data into a form? The user shouldn't have to enter this...
Hi. I'm a freshmen currently in college and I'm taking business info systems right now... and well I'm just being paranoid. Anyway, i really hope this isn't at all too confusing or anything... but
For a hw assignment we where sopposed to like dl this database and just do random commands on it. Anyway, we're sopposed to save it at the end... and email the file to the professor who will grade it.
Anyway, I asked her how she would know if we actually did all the steps and shit... and how she could tell (esp if the directions where like... "1. filter this out." then "3. take out the filter."
she replied that access "saves" all the actions in a database in the document so she will be able to see all the actions that we took.
I did all the assignments, and I'm just being paranoid about my work right now... since it's the new semester and the teacher is SO NICE but very picky when it comes to grading.
My question is just to ask if anyone knows the "secret" command/function/button or whatever you do to basically do what the professor would do to grade my database / ie see that I did everything.
I just want to check to see all my work is good before I submit it to her.
Thank you so much for your time and reading this. :)
I am creating a database for a user that has a health & safety report form on paper that he would like replicated on screen on a form that he could fill in. Is there a way to do this? If not any ideas of which way to go from here would be much appeciated.
