Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Access might be useful if you have a large number of people to cater for, or if you are constantly entering new information. It sounds as if Excel might be more useful in your case. A couple of things that might help you are conditional formatting and filtering.</p> <hr> <p><strong>This will highlight a client if they are due for a delivery today:</strong></p> <p>Create a spreasheet with people's names in column <strong>A</strong> starting on <strong>row 2</strong> (headings are optional in row 1, but the formula below starts with row 2 anyway), put the days they have delivery in column <strong>B</strong> with the full day name written out like this <strong>Sunday, Wednesay, Friday</strong> or whatever. In the remaining column you could put addresses, foods, etc Click on cell <strong>A2</strong> and drag to select all the customers' names, the days, addresses, etc. Click on <strong>Conditional Formatting</strong> at the top of the Excel window.</p> <p>Click on <strong>New Rule</strong></p> <p>Selct <strong>Use a Formula to determine which cells to format</strong></p> <p>In the box labelled <strong>Format values where this formula is true</strong> copy and paste this formula:</p> <pre><code>=COUNTIF($B2,"*"&amp;TEXT(WEEKDAY(TODAY(),1),"dddd")&amp;"*")&gt;0 </code></pre> <p>Still in the dialog box, click the <strong>Format</strong> button, select the <strong>Fill</strong> tab and select a bright colour. Click <strong>OK</strong> to confirm the colour, then <strong>OK</strong> to save the new rule, then <strong>OK</strong> to apply the conditional format.</p> <p>(This formula gets the date with <strong>TODAY()</strong>, converts it to the <strong>WEEKDAY()</strong> number (using Sunday as day 1), converts the day number to <strong>TEXT</strong> with the format "dddd" (Sunday instead of "ddd", which is Sun), <strong>COUNTIF()</strong> will only count the cell if it contains the day (giving 1 or 0), if it contains the day then '1 is greater than 0' becomes TRUE and the cell is conditionally formatted.)</p> <hr> <p>If you want the formula to work for other dates instead of today you could replace the TODAY() part with a cell reference, say G1, then type the date you want in cell G1:</p> <pre><code>=COUNTIF($B3,"*"&amp;TEXT(WEEKDAY($G$1,1),"dddd")&amp;"*")&gt;0 </code></pre> <p>Then type a date like 22/5/12 in cell G1</p> <p>If clients get deliveries on the same day every week, then to see who gets meals on Thursdays you could type Thursday in G1 &amp; shorten the formula to:</p> <pre><code>=COUNTIF($B3,"*"&amp;$G$1&amp;"*")&gt;0 </code></pre> <hr> <p><strong>To create a filter that displays only clients due that day instead of just highlighting them:</strong></p> <p>Create a spreadsheet laid out as before (starting in row 2, with the client's Delivery Days in column B - Monday, Thursday, Saturday) and, instead of Conditional Formatting, paste the formula into a cell in row <strong>2</strong> and use autofill by hovering the mouse over the bottom-right of the cell with the formula so the cursor changes to a <strong>+</strong> and double-clicking. You should get a column of TRUE and FALSE values, as long as you don't have any empty rows. (If you need empty rows, click and drag the <strong>+</strong> down instead.)</p> <p>Go to the <strong>Data</strong> tab at the top of the Excel window and select the <strong>Filter</strong> button. Some down arrows should appear at the top of your columns. Select the down arrow above the TRUE/FALSE column and select only the TRUE checkbox. To display all clients again, select the same down arrow again and select <strong>Select All</strong>. Any time you add a new client to the spreadsheet, hover over a cell in the formula column and double-click the <strong>+</strong> autofill again to add the formula to the new client's row.</p> <p>You could use both methods together to highlight <em>and</em> filter without a problem</p> <hr> <p>There are other things that could help, but it depends on how you operate. You could write a column of 'todays ingredients' and use Conditional Formatting to highlight someone in red when an ingredient matches something they cannot eat. This could be risky if someone doesn't eat fish and you put haddock in the ingredients list because they will not match, of course!</p> <p>If you want free Excel masterclasses I recommend YouTube's <a href="http://www.youtube.com/user/bjele123/videos" rel="nofollow">Bill Jelen - 'Mr Excel'</a> and <a href="http://www.youtube.com/user/ExcelIsFun/videos" rel="nofollow">ExcelIsFun</a></p>
    singulars
    1. This table or related slice is empty.
    1. POCreating a database using Access?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMr.Oz
    1. USMr.Oz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload