Design So Basic I'm Embarrassed To Ask
Aug 10, 2005
Hello all;
It has been a long while since I messed with Access (or any database) and I am having some trouble finding my way around in design and query.
I have a database with approximately 30 tables which are identical. (All imported from the same application.) The tables vary in size from 2 to 100 records. 6 fields in each. The database will be mainly used for retaining historical data. I would like to be able to query all of them at the same time for a keyword in a field for instance, but as well I think that there may be value in keeping them as individual tables.
I’m having trouble getting my head around the easiest way to tackle this simple project, and any guidance would be much appreciated.
willows
View Replies
ADVERTISEMENT
Jun 24, 2005
I'm not sure how to proceed. Any help would be appreciated.
I'm in the military. I have a table with individuals who have been awarded various decorations. With each decoration a set of orders has to be published. If there is a mistake on the orders, another set of orders has to be published (amendments).
The original orders and subsequent amendments are sequentially numbered and dated. For example, GA001, 1 Jan 05; GA002, 3 Jan 05.
I have two tables: (1) demographic and award data; (2) Order numbers (with issue date; y/n field for amendment).
I linked the tables with a common field Table One (one) to Table Two (many).
I built a form for Table one. I want a subform in the master from to show all the orders issued for that person/decoration but I don't know how to show the next available order number without calling up Table two.
Is it possible to show all the assigned order#s/date PLUS the next available order number?
Thanks in advance.
View 2 Replies
View Related
Mar 5, 2008
hi everyone,
as a network engineer, it administrator, etc etc people expect to know absolutely everything about pc's. so here i am.....
a company whose network i look after are using excel spreadsheets on a weekly basis to take orders, wages, running costs etc from their remote sites (currently about 20 sites)
the spreadsheets are accessed via terminal services and are moved via a script at the end of the week so the remote site doesn't have access to the old records and replaced by a new sheet. so lots of spreadsheets floating about.
you can imagine how hard it is to pull past records & compare etc.
now, i'm new to databases and have order a book to gen up on the subject. the sites should only be able to edit the last 7 days of records but be able to see the last years. i can see how a query and subforms can achieve this etc.
each site shouldn't be able to see the other site's records and the head office should be able to run reports etc on each site or as a whole.
i can narrow the time down that everybody is accessing the data. the sites may grow to 50+ but the head office is unlikely to have more than 5 people access the dabase at the same time.
my question is..... (access 2007.... can't see any user security).....
am i better to do an individual database (all exactly the same apart from name, titles etc) for each site that sits in their individual folders via terminal services? then let the head office access each seperate database via linked tables?
my thinking for the above way was security (they can't see each other) and resilience (1 database gets corrupted, the others are still workable) and speed (only a few users accessing each database)
the downsides are obviously the amount of files although this is nothing compared to the excel files at the moment.
any ideas or help would be appreciated.
thanks
louis
View 3 Replies
View Related
Oct 6, 2014
I have had to use my first crosstab queries.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes.
I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
View 6 Replies
View Related
May 18, 2007
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
Thanks
Rahul
View 1 Replies
View Related
May 5, 2005
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3
1 5 7 9
2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN
1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2
2 d e
Table DELTA:
COLA_IND DAT1 DAT2
2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
Any comments?
Thanks in advance...
View 1 Replies
View Related
Sep 10, 2007
Hey,
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
tblLicenseInformation
License_ID
LicenseDescription
NumOfLicensesPurch
SoftwareOverview_ID
tblHardwareSoftwareLicense
HSL_ID
Hardware_ID
Vender_ID
Software_ID
AppEdition_ID
AppVersion_ID
SoftwareOverview_ID
License_ID
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
cheers
View 3 Replies
View Related
Jun 5, 2014
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column
(2) [shift+arrows] to select all of the columns I need
(3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
View 4 Replies
View Related
Feb 21, 2007
Please can you guys give me some basic help, im pulling my hair out over this one!
Attached is a test database which im writing at work, what I would like to happen is that when a new machine ID is selected from the drop down box (combo box) all the relevant fields update with all the information in them.
Please could somebody download my test database have a quick look, see if they can sort it for me then upload it back and explain how they did it.
Thanks in advance
Andrew.
View 6 Replies
View Related
Oct 3, 2005
I will try and be as concise as I can...
I am trying to create a database for our organization that will keep track of our "Gift Fund". I have a table with all of our personnel that includes all of their basic info with a customer ID field as the PK. I then have a table that is for all of the transactions. Deposits, withdrawls, description, date, customer ID to relate the two tables and transaction ID as the PK.
So we have a table with our personnel and a table to record transactions. Transactions occur anytime we put money in or take money out as well as when one of our personnel becomes a member of this fund by paying their "dues".
I have created a form to update our personnel table. It works fine. I also have a form to enter basic transactions. It also works fine. My problem lies where I try to make a form that will add a member to this fund (which in the personnel table is a yes/no checkbox) at the same time entering this as a transaction. Ideally the form would have the user pick personnel from a drop down list, click the yes/no checkbox making them a member and then entering their "dues" as a transaction.
Ultimately I would like to be able present this info in several dif reports showing all the silly statistics that boss' love to see.
Any help would be appreciated and if I have left out any key info, let me knw and I will be more specific.
View 2 Replies
View Related
Mar 1, 2006
Hi there,
Having read many posts here regarding normalization, table designs with a view to avoiding duplication of data, avoiding storing of calculated contols into a field in a table (duplication as well as danger of keeping data updated, etc) - I would like to clarify something...
I've designed 2 databases, one sticking to the rules and one breaking them...
I hear the 'don't store' - calculate on demand principle - and this works well in the case of closing a new record where you 'store' a value by passing a subtotal from a subform to a field in the main form. All that passing of values works but there are time delays. Look at the time delay of summing a range of values in a form footer...bout half a second in general...So in this case, storing values fails...(incidentally is there an 'event' for this time delay a calculated control takes to show it's value in a form? - OnErrorCausedByDelayOfCalculatedControlToDisplayIt 'sValue?? Oops ignore the apostrophe ;-).....).
On the other hand you may want to work through many thousands of records, summarising all your data into reports, charts, etc...It just seems that if you stored each records 'total' (say an invoice total) it would be quicker for to work through thousands of 'ready stored numbers' rather than thousands of 'calculations to get those numbers'...or maybe I'm missing something!?
Sorry for all the fuss, but I'm self taught and I wish I wasn't LOL. I shoulda definitely done college instead of getting hooked on databases in my 30's)
And I'm also sorry if this question has been asked before, even under different wording...
Best regards
dazza61
View 1 Replies
View Related
Mar 22, 2006
A family is to enter basic income and expenses data comprising of date, description, catagory and price. The family don't work on a budget that is lost at the end of the month if it is not used, but rather it accumulates in each allocated catagory. example: Money not spent on a 'holiday' this month will build up each month in the 'holiday' catagory until it is spent.
They need to enter data. Query, add, modify, and delete records. Also, to print simple reports.
1. Is this a big project for a novice?
2. What exactly is required to be done? i.e What do you suggest I need?
3. Would it be better to buy SimplyAccounting Basic if it did the job?
View 1 Replies
View Related
Sep 20, 2005
I have written a basic query that looks at the date field, department field and cost the cost field on a table. What I would like is a form that you can chose a date (19/09/2005) and a it looks at the table and calculates the total sum of money for each department for this date. Ie.
Date: 19/09/2005
Dept 1 = £123.00
Dept 2 = £1234.00
Dept 3 = £123.00
Dept 4 = £1234.00
Dept 5 = £123.00
Dept 6 = £1234.00
I can get the query to work but only 1 department at a time. Any ideas?
I have attached the DB
Thanks
D
View 6 Replies
View Related
May 22, 2006
Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss.
I have two tables
Table1
Code Serial_No Amount1 Amount2
1 ABC 100 200
1 ABC 720 220
4 ABC 150 112
9 PQR 11 25
9 ZZZ 122 44
9 ZZZ 1250 42
Table2
Code Serial_No Amount1 Amount2
1 ABC 24 20
1 PPP 72 22
4 ABC 15 12
9 PQR 11 54
9 ZZZ 22 44
9 ZZZ 150 99
and total of the amount as 844 and 440
I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows
Outpput
1 ABC 100 200
1 ABC 720 220
1 ABC 24 20
Thanks in advance
View 4 Replies
View Related
Jul 23, 2007
I have forgotten everything about Access!
I have two tables: A & B, and I want to join the two by a common field, and only add one field from B to the A table (based on the common field).
How do I accomplish this?
Thanks!
:confused:
View 11 Replies
View Related
Dec 4, 2007
Hey. This is a really basic question but i've looked through a few tutorials and couldn't find an answer
If i have this statement
SELECT * FROM timesheet
WHERE Weekending='07/12/07' FNAME='James.90';
How do i filter it with 2 parameters
such as the Weekending and the FNAME. When i run it, it gives me the following error
Syntax Error (missing operator) in query expression Weekending="07/12/07" FNAME="James.90".
Thanks
View 4 Replies
View Related
Mar 28, 2008
Hi,
I'm trying to understand queries a little better -
If I have a query linked to a Report does the query run first - then open the report?
It's just that If I make a new entry to a table the query seems not to be showing the new entry.
Secondly there are different types of query - what are they for?
EG
Updat
Append etc
Cheers
Andy
View 4 Replies
View Related
Feb 16, 2005
Hi,
Many thanks to those who helped with my last couple of problems.
I've allmost completed my project now and am debugging.
I've found that in order to close one of my forms, which has combo boxes on it, I need to have no values selected in those combo boxes. Could someone give me the visual basic script (or any alternative meathod) for resetting the combo boxes to their default values; i.e. nothing selected in them!
Many thanks,
Robin :o
View 3 Replies
View Related
Jul 6, 2005
I have a table with eight fields. I want to do a chart where I the piles are "tighten" togther two and two. I would like the chart to look something like this (Example):
|_______________________________
|___ _ __________________________
|___|x|______ _ ____ _ _____ _ ____
|___|x|_ ___ _| |___|x|_ ____|x|___
|___|x| |___|x| |___|x| |____|x|____
|___|x| |___|x| |___|x| |__ _|x|____
|___|x| |___|x| |___|x| |__| |x|____
View 5 Replies
View Related
Oct 31, 2005
Ok I’m coding a form, heres what I have done so far
Me.FN LN =DLookUp("[First Name] & ' ' & [Last Name]","tblStaffID", "Staff ID=" & Me.Staff ID")
I have a txt box to imput the Staff ID number in to and I want it to return the first and last name in to another txt box
Am I close??? What am I doing wrong here??
The table this is coming off is just three fields “Staff ID” “Last Name” “First Name”
Can anyone help??
View 9 Replies
View Related
Jan 12, 2006
Hi
Im an absolute noob when it comes to Access so please bear with me
For work I want to make a database to keep track of ordering, so thus far i have made 2 tables: suppliers and products
in my form to add products, i want it to display a drop down with the supplierName in it, but store the supplierID in the products table.
I figure this should be pretty simple, and could easily do it if this were php....
Can anyone help?
View 4 Replies
View Related
May 19, 2006
Hello,
Help appreciated,
trying to create a form so that when I type in a value it will bring back held in a table it will display two fields held in the same row. i.e.
Table 1
no
first name
surname
I want to type the no field and it displays the appropriate first name & surname fields for the row.
View 1 Replies
View Related
Sep 14, 2004
If I am developing a form for data entry into a table, should the various controls in the form be bound to the fields in the table or not?
I have seen that if they are then any entry into the form control automatically fills up the fields in the table.
So, as an alternative, should the controls be unbound and the data entry be through an event procedure with an SQL string??
I will be glad to supply more specific information if needed...
Thanks in advance!
View 5 Replies
View Related
Mar 3, 2005
if i have multiple tables set up with multiple attributes and one common attribute, say email, is there a way for the user to enter once from a site or form or whatever and the email record be filled in all the tables.
View 6 Replies
View Related
Aug 20, 2004
So I have a table with two fields of info that I want to concatenate into a new field. My problem is Field 1 consists of data as such (FI, EDG, EQ, EM, etc...) I only want to concatenate if the value in field 1 is NOT FI (all others are okay). I am trying to no avail to build an if/then statement. Is there an easy way to accomplish this goal and preserve the same # of rows in the resulting query?
View 1 Replies
View Related
Dec 6, 2007
How do I create a form with multiple subforms? I need to create a receiving report that allows for entry of multiple types of merchandise received (tracked by barcode) as well as multiple employees (tracked by employee ID#). I think I need a form with two subforms. Has anyone ever dealt with this?
Thank you in advance
View 5 Replies
View Related
