I have created a database that logs machine downtime. Basically everyday the user will enter downtime for every machine. I would like to create a form that has all the machines listed along with the fields for the user to enter. I have a form set up for one entry at a time, but i would much rather to be able to enter the information for every machine on a specified date.
I have a table that contains all the data for the database (Data), with fields
-Date (date)
-Machine ID (text)
-Machine Inuse? (yes/no)
-Downtime Code 1 (number)
-Downtime Code 2 (number)
-Downtime Code 3 (number)
-Downtime Code 4 (number)
I also have a table that contains all the machine information (Machine/shifts operated), with fields
- Machine (text)
I would like the form to ask the user to enter a date and then list all the machines from (machine/shifts operated), and then have dummy values for fields Machine Inuse?, Downtime Code 1, Downtime Code 2, Downtime Code 3, Downtime Code 4 for which the user can change. After all the values have been changed i would like it to be saved into the Data table.
Is this possible?
Thanks for your help.
6  2110 
I have created a database that logs machine downtime. Basically everyday the user will enter downtime for every machine. I would like to create a form that has all the machines listed along with the fields for the user to enter. I have a form set up for one entry at a time, but i would much rather to be able to enter the information for every machine on a specified date.
Hi. Try the following SQL append statement: - INSERT INTO Data ( [Machine ID], [Date], [Machine In Use?], [Downtime Code 1], [Downtime Code 2], [Downtime Code 3], [Downtime Code 4] )
-
SELECT Machine.Machine, [What Date?] AS TheDate, False AS InUse, 0 AS DT1, 0 AS DT2, 0 AS DT3, 0 AS DT4
-
FROM Machine
-
ORDER BY Machine.Machine;
and see how you get on.
Sample data for dummy machines one, two, three and four: - Machine ID Date Machine In Use? Downtime Code 1 Downtime Code 2 Downtime Code 3 Downtime Code 4
-
Four 12/03/2008 No 0 0 0 0
-
One 12/03/2008 No 0 0 0 0
-
Three 12/03/2008 No 0 0 0 0
-
Two 12/03/2008 No 0 0 0 0
-Stewart
Thanks, that works great! But now what do i use to allow users to enter the information? Forms will not let me access an append query. Also, if different people will be entering information at different times during the day, every time the append query is run will it delete all the previous information?
Thanks, that works great! But now what do i use to allow users to enter the information? Forms will not let me access an append query. Also, if different people will be entering information at different times during the day, every time the append query is run will it delete all the previous information?
Hi Scott. For data entry you define a form based upon your Data table (or on a query which is based on that table) and your users will be able to modify/add details accordingly.
The append query does not delete any existing data; it simply appends values into the existing table, as long as to do so would not cause key violations.
-Stewart
Okay awesome!
If the append query is executed twice, it will create duplicate enteries for the same date. There will be approximately 4 users updating this DB at different times, so i would get 4 times the amont of enteries for a single day. Is there a way around this? Also, is there anyway to remove that warning message that pops up when the append query is executed?
Thanks so much for your help!
Okay awesome!
If the append query is executed twice, it will create duplicate enteries for the same date. There will be approximately 4 users updating this DB at different times, so i would get 4 times the amont of enteries for a single day. Is there a way around this? Also, is there anyway to remove that warning message that pops up when the append query is executed?
Thanks so much for your help!
Hi Scott. It is possible to remove the warning message, depending on how you are running the append, but given you are running it infrequently you might be better to keep it on as a reminder. If you were running the update as a macro you can precede the query with the macro action SetWarnings with its Warnings On value as No, and add the same action in the line after the update query except with Warnings On as Yes.
If you are running the append from a command button you can do something similar in the command button code - precede it with DoCmd.SetWarnings False and follow it with DoCmd.SetWarnings True.
If you are just running the append query from the Queries window you'll just have to accept that the warnings will occur.
If your table design was fully appropriate in the relational sense Access itself would prevent you from storing duplicate row values as these would violate key value constraints. Since it is unlikely that the table key relations are set to involve the machine ID and the date you are just going to have to make sure the query is not run more than once for each date.
-Stewart
Okay thanks. I used a delete query to remove the dummy rows that haven't been updated, this way the append query can be executed several times followed by the delete query after to remove the blank rows.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: RC |
last post by:
I am try test a simple HTML form with PHP
<form method=POST action="testing.php">
Cat <input type="checkbox" name="pet" value="cat">
Dog <input type="checkbox" name="pet" value="dog">
Pig...
|
by: Logical |
last post by:
I have a form which houses basic inputs, as well as a few multiple select
forms.
I need to parse all the 'values' which are in this multiple select form (it
gets manipulated dynamically client...
|
by: Tom |
last post by:
I am looking for some ideas for how to design the layout of the form for
data entry and to display the data for the following situation:
There are many sales associates. A sales associate can work...
|
by: Alex Maghen |
last post by:
I have a master page which contains a general page framework and also
contains a <form runat=server> around most of the content of the page. Inside
that <form> tag is a ContentPlaceholder.
I...
|
by: kschneider |
last post by:
Assume there's a form with it's action attribute all set to post to a
URL, but without a submit control. Form submission is done via a link
and I want to prevent the classic "double submit"....
|
by: Ryan |
last post by:
Ok.. I have a form with lots of stuff on it; a tool strip panel, menu strip,
data binding elements (dataset, binding source, table adapter), tab control
with 7 tab pages, each page contains a...
|
by: Neil |
last post by:
"lyle" <lyle.fairfield@gmail.comwrote in message
news:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googlegroups.com...
Question for you. I'm doing something similar, only, instead of opening...
|
by: billa856 |
last post by:
Hi,
My project is in MS Access 2002.
In that I want to open one form multiple times.
I put one button on my main form.
Now whenever I click on that button than form will be open.
Now when I...
|
by: Scott12345 |
last post by:
Huge title ...
I have db that tracks machine usage for about 30 machines, all the information is stored in a table (Data). Each day a supervisor will enter downtime hours into 5 categories...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
