I currently have an access database for tracking company assets and performing maintenance tasks on them.We also have a number of Stores Stock Items that are controlled under a separate finance package.We currently have forms that create individually numbered "Job Cards" in the Job Cards Table..we input the data and manually type into a Text Box the we have used 'X' number of Store Stock 'Item Y'
I have then use a query to export the data i need (Job number, dates, location and Store Stock Items).But I have to manually re-construct the data and items into separate lines in an excel sheet for a paste operation into the finance package (lots of typing of 16 digit stock codes)
What i want is to use a second table (linked via the "Job Card#" key).It will be called "Stock Items" which has a separate field for each of our stock items.the user then just opens second form while creating the job that immediately creates a new record.The user then simply has to put the number of each item used in the relevant text box on the form and close.
example of datasheet view
Job# __ ItemX __ ItemY
2417_____3_______7
Now, I have all of that working as I want, But i nee to be able to combine the data from each field into a single field (either in access or excel).What I want to see is a field containing 3x ItemX, 7x ItemY, etc
if there are none of one or more items, I don't want anything displayed for that item (blank space is ok)I have 30 or more stock items, so there will be 30 or more columns in the table. the actual data stored in the table is simply the quantity of each item used.
this data is used twofold,on export to Excel I can split the data into new cells and it's used on a despatch note that is returned with the item so the end user can see the items that were used in the repair.So it needs to be done upon saving the record, record by record.
I have a form that is based on a query that returns all the students taught by an indidual faculty member. There are two text boxes which I have added to the form bound to a table used in the query. However when I try to add data (attendance data) is doesn't accept it and simply "pings" each time I try.
I've made a form where the user can fill in a field ID... and than by clicking on a button another form will be opened with the data of that person (with that ID that's filled in). But if the user fill in an ID that is not currently in the database, there will be also shown another form, but than with no data.. Now I want that if the ID is not known, to display a message or something (like "this ID is not known, try again") and not to open the form..
I've used the following code under the button:
Private Sub openenFormulier_Click() On Error GoTo Err_openenFormulier_Click
Dim stDocName As String Dim stLinkCriteria As String
I currently need a way to check to see if a a field already exists. To be exact I have an access application that imports data from multiple sources and saves the file name of these imports into an import tracker table. The table has four fields, file name, Date Imported, Import Type, and Number of Records. I am writing some code that checks a Boolean given by a form. If the Boolean is true It skips over checking.
If it is false It needs to check my import tracker file names to see if the file was already imported. If it was then it will skip over the file and the algorithm will check the next file given by an array. So I was wondering..How would I go about making it check if the file already exists in the table. I was thinking of turning the File Name Field into a primary key, since there will only ever be one File Name of every type.
I have entered one entry in to the database with the correct values (this is record 6) and everytime I enter a new record, the data is the same (I'm using a drop down list) it will automatically appear in the box. But when I goto the table that the data is stored in, each field just has the reference to the first record that the data came from.
Looks a bit like this:
ID Server Name Pattern Engine Program 6 LN1HOME 4.735.00 8.500-1002 5.58.0.1185 7 LN1WORK2 6 6 6
Is there a way of seeing the origonal data in those fields other than the Record ID Number as a reference. :confused:
I know its a lame question, Im just is a learning process at the moment.
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.
I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.
Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)
I have a report that prints invoices, and in the page header area it contains most of the invoice information such as labor cost, customer info, etc. In the detail section of the report i have the line items, and a line contains a part, vehicle charge, etc. Some invoices dont have any line items though, and it seems that when there are no lines(ie. nothing in the 'detail' section), the header information will not appear either. Any ideas on how i can fix this?
As the title suggests I would like to combine the data from two tables.
The first table records customer orders in fields - Part No / Quantity / Reqd Date Of Delivery
The second table is an output from a scanning s/w package we recently installed.
This provides the following data - Pack Qty / Part Number / Date Scanned.
The first thing I did was create a simply summing query to add up all the pack qtys for a specific part on a specific day.
Now comes the hard part.
What I want to do is sort and combine both tables in chronological order.
Where the part number & date for an order and a delivery match, I require the record to be combined to one record (in this query).
Where it does not match, then it should remain as two seperate records.
So for example if you ordered 100 footballs for delivery on 11/11, and we delivered 110 footballs on 11/11, the query would return:
Footballs 11/11 100 110
If the delivery was early / late we would get:
Footballs 11/11 100 0 Footballs 12/11 0 110
Now this last bit is where my queries (simple cross table queries) falls down as I only have three options when creating the join properties, ie both match, all from left table or all from right table, whereas I want all from both tables even if they do not match.
Maybe a query is the wrong way to go, but as the table from the scanner s/w is updated on a daily basis it would seem to me to be the best way forward.
I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.
i.e. order number, price, order date, invoice date
same order number, same price, same order date, different invoice date.
How do I combine the second line with the first so that I only have the data once.
I use a Access database to import data from an email generated from google docs..When the data imports to the table it creates a "Memo Field" with several lines of carriage seperated data containing the Form Field name & answer. (ie: Firstname=John, Surname=smith, etc)Is there any way to seperate this data in the cell into seperated cells in a new record?
I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.
The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).
I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).
(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)
SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder, Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete FROM Suppliers INNER JOIN (OrderDetails INNER JOIN (((Customers INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID) INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account) INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation) ON OrderDetails.Index = SavingsClubReservations.OrderID) ON Suppliers.Index = OrderDetails.Supplier WHERE (SavingsClubAccounts.Redeemed=False) ORDER BY Customers.Name
I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.
SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False) And SavingsClubAccounts.Account Not IN (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False)) UNION (SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));
Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...
I have a table with all repair history in it. Unfortunately, the entry system is such that people can initiate a new record without typing in the correct customer name. So if my customer was "Fry's" it could be entered into the system as "FRYS" "Frys electronics"... etc. You wouldn't believe the ways people enter the same name. Anway, the only thing that links any of these records together is the item serial number. Though one serial number could be under a different name than the other.
Does anyone have any ideas as to how to create a temp table which associates related serial numbers to a single customer name? Even if the user had to select the customer names he want's associated that would be fine.
Note: I should mention that no there is no way to get this changed on the back end. I'm stuck with the tables I have.
I've been given the task of combining data from three locations into one database in Access, something that is relatively new to me. I'm trying to decide whether to have the locations enter data into an Access database or into an Excel spreadsheet at each location. Regardless of the program used to enter data, I would probably like to link each of the files to my main Access file and combine the three into one database there. I do not want the locations to add data directly to the main database if possible.
My question is, would this be done by an append query? If so, how? Also, my thought is that the data at each location would be added to every day. I would need to be able to add the NEW data only to the database without duplicating the previous day's data.
I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.
I've got a piece of VBA scripting which runs as an event linked to a button on my MS Access form.
I maintain a database of members of staff at my organisation. It's pretty outdated...
I'm basically wanting to pull in their updated data (extracted from on our payroll system) from a spreadsheet, into a form, when clicking a button on a particular person's record.
The function "CStr(DDERequest())" converts the cell number into the readable data, however I seem to have whitespace below the value.
What would I need to do to strip out this whitespace? Would I use strtrim? If so, I am unsure of the syntax... how would I incorporate strtrim into the above?
I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.
Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.
I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.
Is it possible to append two queries? The queries have the same layout and labels...it's just a matter of combining the data into one datasheet view.
The two queries are below:
1) SELECT prc.Market AS Geography, trp.Product, trp.Indication, trp.[Variable Name], trp.Period, Round(prc.Index*trp.[Variable Value],2) AS [Variable Value], trp.Outlet, trp.Daypart, trp.[Program Name], trp.Len, trp.Creative, trp.Campaign, trp.[Campaign Name], trp.[Media Type], trp.Vendor, trp.Channel FROM tblTRP AS trp, tblTRPpercent AS prc WHERE (((trp.Indication)=prc.Indication) And ((trp.[Media Type])=prc.Type) And ((trp.Geography)="National") And ((trp.Month)=prc.Month));
2) SELECT prct.DMA AS Geography, tv.Product, tv.Indication, tv.[Variable Name], tv.Period, Round(prct.percentUniverse*tv.[Variable Value],6) AS [Variable Value], tv.Outlet, tv.Daypart, tv.[Program Name], tv.Len, tv.Creative, tv.Campaign, tv.[Campaign Name], tv.[Media Type], tv.Vendor, tv.Channel FROM tblTVOtherMetrics AS tv, tblDMApercent AS prct WHERE (((tv.Geography)="National"));
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
