In this database, there is a table of users (name, address, phone, etc.) There is also a table of 35 groups. Each user may be in any combination of 1 or all of the groups. The list of groups is not static. At any point in time, I need to be able to show that Bob is in groups 1, 19 and 35. All I can think to do is list all 35 groups in the user table, then make each one a yes or no field. HELP! There’s got to be a better, more efficient way.
View RepliesPlease be patient with me.. really a beginner here. Thanks. :)
Here is my question:
I have multiple tableS (and by multiple, I mean a LOT of tables.) They are all similarly designed, with mailing addresses of potential customers. They were all entered at different locations and different timing. What I want to do is to take out all customers from a particular country at one go. The simple and tedious way is of course to merge ALL the tables then run a query. But is there a simpler way?
Somebody wrote me a SQL thingy, that helps me remove all the duplicates, so that is not a problem. But the merging of the tables is too tedious considering the number of tables involved. I myself have around zero knowledge of SQL, but if the solution is in that direction, please let me know, and I will find someone who can help figure it out.
THANKS!
I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.
Will this cause problems down the road?
Thank you once again
John D
All,
I have a search form (continuous) which filters results from the main table. I can then double click on one of the filtered results to open a new form (single) showing all the fields for that entry. Fine so far.
My question - There are ten different types of operation that can be displayed, all with slightly different relevant fields.
Which of these methods is more efficient:
1. Make ten custom made forms (the correct one displayed depending on the selected operation). - Easy to do but needs ten forms.
or
2. Have one form with all the fields added (only the correct fields made visible by code). - Complicated code.
What do you think ?
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc)
ID
Inckey
Exclude Table (tblex)
ID
exkey
List Table (list)
ID
Company
Address
City
State
ZIPCode
…
ZIPCode Table (ZIPCode)
ID
ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, *
FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*"
ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
I have an input form that has many fields, how do I go about fragmenting the data collection.
I thought I might group related data fileds together ie personal details, contact details and have a separate form for each data grouping or one form and make the fields invisible after they are filled in and then make the new data group fileds visible ie overlaying them so it appears to be a new form. I am not sure and would appreciate guidance.
regards
Peter
I'm trying to find the best way to organize a report that will have ALOT of fields for an individual record. Because it is for an individual record not a group of records (and I want it to be dynamic) I'm thinking of doing a filter & query to fill in report with necessary details. This report is going to contain ALOT of fields, even the wizard that I used based on the query said that there were too many fields, but I can't cut down.
View 4 Replies View RelatedHi,
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
Thanks,
Adam.
Hello,
I have an option group with check boxes on my form that updates a yes/no field in a table, so the choices the user used to have were check "something A" or "something B". Now I have to add a third choice of "nothing" to this.
Is there a way to achieve this with my current setup, or do I need to add a field to my table so that I can have the choices of "something" or "nothing", and then of "something A" or "something B" if the user choses "something"?
Thank you.
Hello,
I am figuring this must be an easy question. I have combo boxes setup and working great. I just noticed that I can put my own text into the box and not be limited by just the choices in the combo box. If that makes sense.
When I try to lock the boxes then it doesnt allow me to select. I want the combo boxes to be the only selection they can make, and not be able to put their own text in.
Attached a screenshot to help clarify.
Anyone know what option I am missing?
Thanks.
Hi All.
I currently have a query that runs a report. The "Project_Status" field of the query is decided from a combo (cboPS) that is on a form (frmReportManager). The user selects the status from the combo and clicks a button to run a report with that status. That is all OK.
What I would like is 4 tick boxes on the form with the 4 project Status (Ongoing, complete, invoiced and quote). The user would then choose the status types he would like in his report, not just the one type as in the combo.
How do I go about referencing the 4 text boxes for the status types, to the one field in the query (Project_Status).
I have tried using And and Or, but with no luck.
Czn anyone help?
Many Thanks.
Frank.
OK, this has to be simple, I know...But I cannot figure it out.
I have a parameter query that works great, however, it has become complicated to remember all the values the field can have and so sometimes it is difficult to find the info needed. How can I do it so that instead of having an empty box pop up when the query needs my parameters, I get a list of possible choices??
Appreciate the help
I have a combobos using a table to look up its values, no problem. The table could have several identical values to place in the combo box ( same city, State multiplt times). I want to group these so that the city, state will only show once in the combobox, hence makiing the list a little shorter. is the best way to do this by creating a new query to group the choices and base my combobox off this new query? or is there another way from within the combobox properties?
Thanks all
Kevin
I have a combo box populated from a table containing names. Whomever processes the order selects their name from the combo box. Some entry persons have left the company but I cannot delete their names from the table as the orders are stored with their names and I want to maintain that history. Is there a way to hide their names from being displayed in the combo box?
View 2 Replies View RelatedI would like to sum up choices made by a user. I'm not sure whether I should create a new table and a couple of fields (not including primary key) with Fruit & Fruit Value.
Where: Apple (Y or N)=4 or 0, Oranges (Y/N)=3/0, Bananas (Y/N)=2/0, etc. As the user chooses the fruit, the value rises.
For example, if the user selected Yes for APPLE and ORANGES the sum is 7-- or if the user selected ORANGES and BANANAS the sum is 5, etc.
Is it possible?
I have 2 cbo boxes : Year and Company
A certain year might have multiple entries for the same company. I want to have the company listed once instead of multiple times. How do I do that?
Thanks.
there could be one or more categories for each person, and in the end there could be a lot of categories, certainly 20+, but the idea is that i could add more when needed.
What's the best way to store these data?Should i create a new table called groups and create a coloumn for each category and write the ID there? Should i add one field to the common table and store all interests there as csv? Many coloumns?
how to collect these data? i don't want text field due to spelling errors and inconsistency (don't want someone to add automobiles and the other cars). Plenty of checkboxes? Multichoice box?
Suddenly most of my ribbon functions are greyed out. I noticed it when I was trying to change a simple format. I tried closing outlook and reopening it but nothing is changed. I also tried other databases and still no dice.
View 2 Replies View RelatedI am running Access 2010 on a Windows 7 platform and attempting to write a simple database for a local charity, and whilst I have basic skills in Access I would not put my level higher that enthusiastic amateur.I have a couple of fields, one called 'Income' which is a simple currency field. The second field is called Frequency and is a combo field where the user can choose from Weekly; Monthly; Quarterly; or Annual.
The database user enters data taken from a survey sheet which captures the income of an individual along with the frequency of the payment.The requirement.I would like to create a calculated field which gives the annual income.
Example:
Income = $5,000 Frequency = Monthly
the calculated field would return (5,000 x 12) = 60,000
Equally
Income = $1,000 Frequency = Weekly would return (1,000 x 52) = 52,000
I have a database which keeps track of a trucking business. Each truck has a trailer number. I have no problem having a query prompt the user to enter a single trailer number to query on. What can I do when the customer needs to select more than one trailer number to query on?
View 4 Replies View RelatedThe queries go like this at present :
SELECT TOP 1 [Table].[QuestionText], [Table].[Answer] AS CorrectAnswer
FROM [Table]
GROUP BY [Table].[QuestionText], [Table].[Answer], rnd([IDQuestion])
ORDER BY rnd([IDQuestion]);
SELECT TOP 3 Table.Answer AS Correct, qQuestionTextAndAnswer.QuestionText, qQuestionTextAndAnswer.CorrectAnswer
FROM [Table], qQuestionTextAndAnswer
WHERE (((Table.Answer)<>[qQuestionTextAndAnswer].[CorrectAnswer]))
ORDER BY Rnd([IDQuestion]);
These queries are displayed now in an Access form "frmQuestions" which is applied to "Table" that has three columns id, text , and answer. The result is one question and four suggested answer with one only being correct.The arrangement of the answers is randomized--
but the choice of the question is not realy random....it always starts with the same question as it relies only on rnd.
can I improve the queries and randomize and rnd in the same ...or else can I switch these queries to be used from vb6 code and achieve that result.
any help would be appreciated.
By the way I am really a beginner at this who is seeking help from the experts.
Is it possible to create a field in a table that stores only two possible data choices: a check or null value ?
View 6 Replies View RelatedI have a table that has the list of "Project design" choices, and I enter in there the choices that a project can be. I then have that table related to a junction table that has the "Project design choices" linked to the "project code." Anyway, I THOUGHT that one of the bonuses to using Access was that if you see something spelled wrong, you could fix it in one table and it would fix it everywhere. However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records.... ok so, if this is bad design.. I may have to leave it because I have spent countless hours doing data entry for this..
View 8 Replies View RelatedI have an access form that has a dropdown box list of 10 choices.
For example:
The form states please choose 1, 2, 3 or 4 then the choice should print in BLUE.
They choose 5, 6, 7 or 8 Then it need to print in RED.
So forth and so on.
I am sure there is a code I need to type in the conditional formatting area that would reflect if [1], [2], [3], [4] then I choose the red color.
I do not know how to use VBL or coding.
Been a while since I put hands on access. Working with a very simple database.
Working with 1 table, 1 form, 1 query.
Query has several fields. Field of interest contains names of counties.
In query design view I can type, "CountyA" Or "CountyB" or "CountyC" in the criteria and the desired results are returned.
On form I've created a text box and an open report button. The report I'm trying to open uses the query as the data source. I reference the text box on the form as the criteria for the query as follows:
[Forms]![frm_LMIBasicReport]![txtCounties][Forms]![frm_LMIBasicReport]![txtCounties]
If I enter the the name of a single county into the form it executes perfectly. If I try to enter multiple counties it fails. This is true whether I just enter the county names or replicate the exact criteria string I use in the source query. For example
CountyA works and returns desired values for County A
CountyA Or CountyB or CountyC fails. The report opens but no records are returned
"CountyA" Or "CountyB" or "CountyC" = exactly how it is entered in the query if I'm not using the form opens report but no records are returned
How can I pass multiple values from a single text box to the query.
I would like to create a combo box (or something similiar if a combo box simply can't do it) where the list of choices in the pulldown shows only the matches of what a user types in. The list of choices are coming from a field in a table. Also I would like the "search" of the user's input to include what is "within" each choice, e.g. if a user type "ber", then valid results should be bertha, october, robert.
View 1 Replies View Related