Hi all: MS Access 2003. I have two text fields family_name and given_name I need to extract 1st, 2nd & 5th letter of family_name along with 2nd & 3rd letter of given_name to form a link_key field. If either name has insufficient letters the missing letter is replaced by the numeral 2. Does anybody know how to help me acheive this please!
View RepliesHi all,
I am new here, I am never one to ask for help, but I am desperate to get some assistance and hoping someone may be able to help me. I have just basic knowledge of MS Access, but no where near the skills needed to acheive what I am needing.
To explain, I am involved with a national memorial of all the Truck drivers who have been killed on our roads. I have a database in access, (near 1000 individuals and sadly growing) but wanting to create it into a form style. I also need to add a photograph of each deceased person and a second photograph of the plaque which they are on. Years ago I saw a form which I was impressed with, it looked so professional but never had the oportunity to investigate or disect it :)
I am hoping sks who will enjoy the challenge may be able to assist me.
more about the memorial can be seen at www.tarcuttamemorial.com
Thanks Dianne
I read about your different topics/discussions but still have issues.
The group I work for needs to track Customs entries, compile them for payment and make reports. They used to track the entries in Excel but they wish to do this in a Form = INVOICE TRACKING, be able to query and make reports. My Access knowledge is limited but I have created two tables and the above mentioned Form
The main table= Invoice Tracking (as per the group request) as 37 fields. Starting at the 8th field, data is updated by the users on a daily basis.
The source table = XLS 1127 as 7 fields and is refreshed and updated 2-3 times weekly by a live report on the Internet.
Up to now, when I need to update the main table, I run an APPEND QUERY. Once this is done, I run a FIND DUPLICATE QUERY and I delete the duplicates manually because some of the first 7 fields will get updated this time or some other time and therefore it will create duplicates Entry Nbr in the Main table. Meanwhile the other 30 fields are populated by users with the information supplied by the invoices.
Is there a macro or an expression that could be created to avoid the Query from selecting the duplicates randomly? I have a very basic knowledge of macros and VB but would it be possible to write something that would be like In record #1, IF the field 2 or 3 is empty than look at field 9 and others and if Not Null then get the data in the duplicate record #2, merge the
information from the two records??
When the Main table will grow bigger, it will be too much time consuming to delete XXX duplicates and can cause errors.
Thanks
Hello, I am working on a database of translators. Each of these translators can have multiple Source Languages, and multiple Target Languages. I understand that I must use a many-to-many relationship, but as the Language fields for both Source and Target Languages comes from one table (tblLanguages), I am having difficulty conceptualizing how I can get the table relationship to produce the desired effect; i.e. having a datasubsheet for each translator with fields for each their Source and Target languages.
My current table structure:
tblTrans
TransID
(name and contact data)
tblLINK_Language_Trans
TransID
LanguageID
tblLanguages
LanguageID
I thought that maybe if I create a separate LINK table for each Source and Target Language, I might get the desired effect, but this was futile, as only one datasubsheet was shown.
Any advice for a humble Access beginner would be GREATLY appreciated!!!
Thanks!
Blair
Hello,
Does anyone know of an off the shelf application I can purchase to allow users access to my Access database over the web? I have select data I want to allow my customers access to dynamically via the web.
I am familiar with ASP however I am not at a level to develop something from the ground up.
I have access to a Windows Server, an established database and a great desire to get this done.
My vision is to have a daily upload of my back-end (for the sake of current data)to the web server for access via the ASP front end or whatever the case may be.
I am sure to all who read this you understand what I am looking for.
I will definitely entertain hiring someone to work with me on this project if need be. Best case scenario is someone has already done the work and I can just buy it...
Thanks.
Fen How
Hi guys,
I need some help here and any would be muchly apprieciated.
I am building a database for a theme park fast pass system where a customer can book themselves a place on a ride at three session times a day.
Now my problem is, that I need to generate a card number. 5555 1946 as the first 8 digits (this always stays the same) and then the last 8 digits is the customer ID number, which is in the same table. So there are 16 digits in total.
Let me give you an example:
A new customer registers and are assigned a customer ID of 1000 0001. Therefore the Card Number for that customer must be 5555 1946 1000 0001.
I have used auto number for customer ID so when a new customer signs up, there ID is 1 more that highest already in te database.
Simple question really...but how is this done?
A million marks for someone who breaks it! Hehe
Thanks
The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.
What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.
First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.
As an example, if I had a product like this:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210
Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.
So in the new table, I would have:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210
Hi all,
I've read this forum for quite a while, but just now registered and this is my first post. It's full of great information and I really appreciate all I've learned!
I'm looking to contract / hire an Access Developer for a small project. Essentially, it's taking a CSV file, importing it into Access, then exporting the data within certain parameters into an Excel spreadsheet. There are a few other small details that pertain to how the data is handled in the actual db itself, but that is the overall picture of the project. It's rather small, but is above my Access knowledge level.
My most important goal is getting the DB functional, but then taking the finished product and breaking it down to learn from it.
Please PM me if you're available to assist on this project. Have a great weekend!
I am looking to create a catalog database. It is to serve as a finished product warehouse, as well as a product development tool. There are a number of fields, most of which are straight-forward.
One large problem I have is that the product descriptions are roughly 700 characters. Is there a way to have this information entered into Access and stored either in this database or elsewhere?
Any help would be GREATLY appreciated.
Last week some very altruistic folks here helped me solve a many-to-many relationships quandry, and I am hoping that there may some others out there who can help with another question.
I have a feeling this is simple, but I can't quite get my head around it, and have not been able to find it in the past forum threads, but....
I want to create a tblJobs that has information about our clients' Companies, as well as Contact. As there can be many Contacts for each Company, my current setup looks like this:
tblContacts
ContactID (Pkey)
Name
Lookup_to_qryCompanies_to_Contact(shows Contact's company)
tblComanies
CompanyID (Pkey)
Company Name
(Client info is included as embedded table based on qryCompanies_to_Contact)
qryCompanies_to_Contact
Company (from tblCompanies)
Contact (from tblContacts) (one-to-many)
In my new tblJobs, I would like to have a combo box in which I can input the company name, and then the next field will have another combo containing ONLY names of the contacts affiliated with the chosen company. (I will also eventually like to make a form that allows the same function, but am assuming that the process will be the same for updating the table trhough a form as it would be updating it directly).
Thank you for any advice!!
Blair Sly
I've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.
What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(
Help!
Hi,
I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.
Any ideas on how I achieve this through an Access query?
Many thanks
Clay
Hi Everybody,
I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.
I'd better give an example:
Table: Kid
Fields:
Name: Ben
Activities: Soccer, Tennis
Name: Dan
Activities: Soccer
Name: John
Activities: Football, Origami
.
.
.
I want to show in a query all the Names which have a "Soccer" substring in their Activities string field
Need your help ASAP !!!
Hi all,
In my query I can query for ODD or EVEN Street Numbers using the syntax below:
OE: [PROPHOUSENUMERIC] Mod 2
This works fine returning just odd or even numbers.
Now the problem I am facing now is that I wan the same query to return both Odd and Even Street numbers if I did not ask for either Odd or Even Street Numbers.
Is this possible. I have search hi and low in the forum but no answer. I have attached the file I am working with this post.
Thanks in advance.
matomo :confused:
Can someone help me out with writing the expression to pull out the IDs that are duplicates?
I know it is something like count >1. thanks!
Hi
I am just wondering if there is any way that you can use access to input information into a letter
i.e. you could just click a command button and it would print a standard leter with the details from an address on access e.g. name etc
Thanks
Hi,
I have a date field in one of my queries (01DEC2004). I have a pivot table linked to the query. I would like the users of the pivot table to be able to group the data by month. Currently the group feature will not work. Does anyone have a suggestion on the best way to extract the month from the date field?
Could I create a new column in the query called month and use an expression to extract the month?
If so can someone tell me how to key the expression to do this?
Thanks for any help.
Was just wondering how i go about pulling out a range of records. I do not want to use my Primary Key autonumber value as some records have been deleted and selecting a range of 10 to 50 for example will not guarentee 40 records. Is there some way i can use an SQL command in the SQL View window to achieve this?
Cheers
I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.
I have a field called ITEM_NUM in a database with the following structure:
XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY
I need to extract the Y portion of the data and a Query format would be the best.
FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.
thanks for your help!
You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:
Field 1
DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5
I need it to read like this:
Field 1 Field 2
DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5
If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.
Thanks,
Amanda
Hi,
I have a long list of customers / suppliers and their VAT numbers. I would like to create a query that shows only the customer/supplier number and the first 2 characters (countrycode) from their VAT number. for example
Supplier 3 VAT number NL80980808
endresult should show:
3 NL
Is there anyone that can help me out??
I probably have an easy question for one of you out there, but I just cannot get it myself.
I need to extract the data between two characters in a string.
The data will be similar to this:
T4454: Text Text-Text: $296.07: Text Text
I need to get the dollar amount between the dollar sign and the colon on the right of it.
So far, I have this for my query:
Amount1: Mid$([subject],InStr([subject],"$"))
Which gets the amount to the left side, but also has the $ in it.
In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00
Thanks in advance for any help you can provide
If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.
e.g.
Table -> 1000 records
records which belong to categoryA -> 200
extract 20 random records from the table which have a value of categoryA inthe cboCategory field.
Thanks for any suggestion.
Hi,
I have a combo box, which the first and last name of an employee is combined using trim. Does anybody know of a way to seperate both fields as these would be used an a query so I would need them seen as 2 fields otherwise there won't be any matches.
Cheers,
Ben
Hi, I have an address database and in the zipcode/postcode box, i want all letters entered to go to capitals ie rj20 4ls would go to RJ20 4LS after update. how could i do this, what do i add to text boxes after updat?
Thanks
Alex
How would I about filtering out all results that contain a letter in them? The entries are in a field called Code, and I only want to display the resources that don't have a letter in the code.
View 1 Replies View Related