Birthday query for many children in one record

I have a database for the church, it has one main flat table only.

Each record for the parents have field for child1 name,
child1birthday, child2 name, child2birthday, child3 name,
child3birthday - up to 5 children.

I am having difficulty trying to get a report to list the birthdays of
the children like this

parent name, child 1 name, child 1 birthday month etc. so you get
something like this:-

Parent John Smith, child Mary Smith January 3
Parent Fred Jones, child Bert Jones March 4
Parent John Smith, child Jane Smith March 6
Parent Harry Brown, child Jane Brown March 6
Parent Bert Taylor, child Anne Taylor December 18
Parent Fred Jones, child Sue Jones December 21

This will enable the children to get a birthday card from the church.

I have tried putting the childrens details on separate tables and also
setting up a query for each child and then running a new query for the
children using these expressions:-

Expr1: Month([baptism_an1])
Expr2: Day([baptism_an1])

but cannot seem to get the right data out. Is there a way I can do
this?

I have looked at the listings here but cannot seem to find the answer.
Any help would be much appreciated.

0
claire
9/7/2007 6:24:53 PM
access 16762 articles. 3 followers. Follow

1 Replies
943 Views

Similar Articles

[PageSpeed] 53

Claire,

Given your table structure (you really need to normalize this so that you 
have a Children table that map to parents), I would recommend you start with 
a Union Query that would look something like:

Select [Parent Name], [Child1 Name] as [Child_Name], [Child1Birthday] as DOB
FROM yourTable
WHERE [Child1 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child2 Name] as [Child_Name], [Child2Birthday] as DOB
FROM yourTable
WHERE [Child2 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child3 Name] as [Child_Name], [Child3Birthday] as DOB
FROM yourTable
WHERE [Child3 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child4 Name] as [Child_Name], [Child4Birthday] as DOB
FROM yourTable
WHERE [Child4 Name] is NOT NULL
UNION aLL
Select [Parent Name], [Child5 Name] as [Child_Name], [Child5Birthday] as DOB
FROM yourTable
WHERE [Child5 Name] is NOT NULL

Save this as qry_ChildDOBs

Create a new query based on qry_ChildDOBs that looks something like:

Select [Parent Name], [Child_Name], Format([Dob], "mmm dd")
FROM qry_ChildDOBs
Order by Format([DOB], "mmdd"), [Parent Name]

HTH
Dale

-- 
Email address is not valid.
Please reply to newsgroup only.


"claire" wrote:

> I have a database for the church, it has one main flat table only.
> 
> Each record for the parents have field for child1 name,
> child1birthday, child2 name, child2birthday, child3 name,
> child3birthday - up to 5 children.
> 
> I am having difficulty trying to get a report to list the birthdays of
> the children like this
> 
> parent name, child 1 name, child 1 birthday month etc. so you get
> something like this:-
> 
> Parent John Smith, child Mary Smith January 3
> Parent Fred Jones, child Bert Jones March 4
> Parent John Smith, child Jane Smith March 6
> Parent Harry Brown, child Jane Brown March 6
> Parent Bert Taylor, child Anne Taylor December 18
> Parent Fred Jones, child Sue Jones December 21
> 
> This will enable the children to get a birthday card from the church.
> 
> I have tried putting the childrens details on separate tables and also
> setting up a query for each child and then running a new query for the
> children using these expressions:-
> 
> Expr1: Month([baptism_an1])
> Expr2: Day([baptism_an1])
> 
> but cannot seem to get the right data out. Is there a way I can do
> this?
> 
> I have looked at the listings here but cannot seem to find the answer.
> Any help would be much appreciated.
> 
> 
0
Utf
9/7/2007 6:50:02 PM
Reply:

Similar Artilces:

Table query
Assume that I have a data entry table where the last column displays each row total. After the table there are several rows that display a Grand Total and analysis on the table entries. How would I add extra data entry rows to the table without having to make any changes to the existing formulas? Thanks Brian Tozer Brian, Simplest way is to insert a blank row above the totals, set the formula to span into that blank row, but always insert the new rows above that blank row and the formulae will remain okay. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (r...

Modifying a Query To Reflect Business days
I am using this: SELECT Trades.Month, Trades.FirmId, Sum(Trades.TradeVolume)/20 AS AverageTradeVolume FROM Trades GROUP BY Trades.Month, Trades.FirmId; The "/20" is an attempt to get the average daily volume (20 business days per month). However, if I am half way through the month, the statistic get skewed. Is there a way to get the average daily volume that is pased on the actual number of business days that have elapsed ? Thank you in advance. I do not know how accurate you want to be but try this --- SELECT Trades.Month, Trades.FirmId, Sum(Trades.TradeVolume)/Day(Date...

Birthdays
How do you ignore the year on a birthday, but use the month/day within a specific perimiter? I want to know if a birthday will be celebrated within 2 dates, and include thier current age. ...

SBS 2008 R2
SBS 2008 R2 - Intel XEON quad, 2 NIC MB NIC #1 WAN -> 192.168.1.3 NIC #2 LAN -> 192.168.16.xxx I am building SBS 2008 R2 as a replacement for SBS 2003 R2 on a new system box. In the configuration, Connect to the Internet, the wizard finds the NIC #1 connected to the router and in Network and Sharing Center, it is labeled WAN. The NIC #2 is labeled as the LAN. However, in DHCP, the scope is on the WAN not the LAN; scope for (192.168.1.xxx). In SBS 2003, the scope is on the LAN side (192.168.16.xxx). Why would the internal clients have an external address? My questi...

sums on a query
Hello, I’m building a query that I would like to sum quantities of a part numbers used on multiple Purchase Orders Example PO Item # qty sum 16 100 2 5 27 100 1 5 39 100 2 5 19 200 2 7 26 200 5 7 Thanks -- JB Use the 'Totals' button in the query designer (see toolbar in query, look for the E-sign) Then group the field you want for instance the 'item' field, just above the criteria field use the arrow for the group by.. hth -- Maurice Ausum "matjcb" wrote: > Hello, > I’m building a query that I would like to sum quantities of a part num...

FA Query
I am wondering if I can change value for fully depreciated assets to 1$ instead of 0 $ , and if I am going to run depreciation again for this asset the system will not change the 1$ value. I mean can I keep 1$ amount for all fully depreciated assets in the system? Any help would be appreciated. Enter a $1.00 Salvage Value for each asset, You'll need to recalculate depreciation on those assets but it should leave you with a $1.00 asset value. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your GPtip42today at www.gp2themax.blogspot...

only one page of website shows
Hi, only one page of my multi-page Publisher website is showing when i do File, Web Page Preview. Same thing when i actually publish to the web, just see the first page and no indication that there are even multiple pages. I tried saving as Single File Web Page and also Web Page but didn't make a difference. It does say Web Publication at the top of the Publisher screen. this happens even for the 3 page wizard-created website. Help!!! Thanks!!! david wrote: > Hi, only one page of my multi-page Publisher website is showing when > i do File, Web Page Preview. Sam...

One Note 2007 Won't Keep Formatting in Paste
Hi, When I paste from various applications into One Note 2007 (part of Office Ultimate 2007), the text formatting is often stripped out in the process, even though I specify to "Keep Source Formatting." If I use Word as an intermediate host, then the formatting remains as it should be (i.e. copy first to Word, then copy and paste from Word to One Note). This is definitely a problem specific to One Note, since the same text copies flawlessly to other applications. Is there a workaround or patch to fix this irritating problem? I shouldn't have to use a third...

Still confused on relationship queries and similating a vlookup
Normally I would just dump this into excel, but the file that I am dealing with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating th...

Parameter query criteria input in Access Page
I am using a parameter query for an Access Page. When the prompt comes up to enter the required data, the dialog box will not show all of the text that I used as the criteria prompt. It works OK when you just run the query, but when using the page, the text is clipped off. Here is what I have as criteria: [Enter Description- options are Round, Square, Rectangular, Oblong] When the box pops up to prompt the user for the input, it comes up: Enter Description- options ar The rest of the text is clipped. Any help is appreciated. ...

Update installation query
I have Windows XP Home SP3. A scan with my "Belarc Advisor" indicates that update KB923561 has to be re-installed. Yet the Windows update website shows that this update was successfully completed on 16-04-2009. Is re-installation really necessary now and how do I do this? "Jan" <Jan@Jan.com> wrote in message news:eJ3pSqUBLHA.980@TK2MSFTNGP04.phx.gbl... >I have Windows XP Home SP3. A scan with my "Belarc Advisor" indicates that >update KB923561 has to be re-installed. Yet the Windows update website >shows that this update was suc...

Merging Customer Records
I have two customer records that I need to merge into one. How do you do this? I am using version 8.0. I looked in help, however I can't find anything on the subject. Also does it merge the history info as well? Thank you! Jeremy Jeremy, There is a utility you can buy from Microsoft through your Great Plains partner called Customer Combiner - it does exactly what you're asking. Without it, the only way would be to manually transfer either the balance or the individual open transactions from one customer to the other. There really isn't too much you can do for hist...

Trying to display records that are 6 months old
I have a database which lists employee details including there start dates. I'm trying to produce a report/query which return employees that have been with us for more than 6 months. The database has a StartDate field where we have entered the date they started with the company & this is the field i need to query. Any help would be much appreciated. Regards Bash In the Criteria row of your query, uner the StartDate field, try: <= DateAdd("m", -6, Date()) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/t...

how to move from our current exchange server to a new one???
We are moving from an exchange active passive cluster to a single exchange server (brand new compaq). My plan (in theory) is to disconnect the current exchange server, build the new exchange server with the same name, install exhange in disaster recovery mode, and then restore the databases from tape. Does this sound correct to you guys? Are there any documents that would apply to this situation? thanks, Matt Why keep the same name? Build your new server alongside the old and move the mailboxes across. If you're worried about the new server name meaning that you'll need to v...

Assigning F key to checking of one account only
WIN XP HE, OL 2002 Hi, Can I assign an "F" key for "Send /Receive" of one my email accounts (only that one)? I use F9 a times for the whole group together but haven't figured out how to do something similar for a chosen account (without creating a macro). Any ideas? Tx, S The keys in Outlook, unlike Word, are hard coded. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.c...

Can I build a calendar from a query?
Here is what I am trying to do. It's very simple, just don't know if I can do it in Access: I work in a department that conducts seminars throughout the year. We have the dates set for next year's seminars, and I would like to create a calendar in Access that will mark the dates (on an annual calendar) for when we have to do a certain task in preparation for upcoming seminars. Confusing? Let me explain: I have built a table with Seminar Names & Seminar Dates that looks like this: Estate Planning Seminar 2/18/2008 PGA Seminar ...

lotus approach queries VS access queries.
Hi, We are migrating from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Front-End Server and one Back-End Server
I had a multiple Back-End Exchange Server enviorment (including a cluster). All had SSL and Forms-Based authentication (except the cluster). I implemented a Front-End Server. Now I know that you are supposed to take away SSL from the Back-End's (although I would like to keep it as a secure backup plan) but regardless.. I actually tested with the front-end server and I could logon to the front-end OWA Forms-Based SSL to mailboxes on all my back-end servers (which still had forms-based SSL) with the exception of one back-end. When I try to logon to a mailbox on this one back-end (w...

how to access data in a query
I don't know how to set the source property on a query? or how does the query know what db to query? It now says (current) so if you have another db open then it queries the current db when you open the query in datashett view? I'm assuming this is what is meant by the query source property? I have an excel spreadsheet that I would like to populate my db so I can test the query. I tried setting the source property which says (current) to [tables.tblTest] on the query just to get some data in it says the name is invalid. (I imported from an Excel spreadsheet some test data into...

How to write this query?
Hi, I'm using SQL Server 2005. I have a query that returns multiple rows in which all the column values are the same except one ... Results id text category ===================== 2 Hello ABC 2 Hello DEF Does anyone know how I would write this query such that I could combine into a comma-separated list, the "category" column, so that the results would return as a single row -- e.g. id text category ===================== 2 Hello ABC, DEF Thanks, - Dave Search the web ...

Calculating the Difference in a Query
I need to calculate the difference between two fields in a query. The query is called “qryRemote” and within the query are the fields “10-8Time” and “10- 97Time”. The data entered into these fields are in the format “0000” through to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum the results and display the result in a report. For example, after the query is run the results are: 10-8Time 10-97Time --------------------------------- 1307 1331 1858 1909 2018 2023 First the difference is determined: 10-8Time is subtracted form t...

Simply query?
Hello All I'm obviously having a bad day query-wise, as this is the 2nd problem I've needed help on! What's wrong with this: SELECT practices.[prac name], practices.[20056 EOY], DMax("[x confirmed]![entered]","[x confirmed]","[x confirmed]![month name] =""march 2010"" and [x confirmed]![practice] = [prac name]") AS Expr2 FROM practices WHERE (((practices.[20056 EOY])="6")); It works fine without the 2nd criteria in the DMax expression (i.e. without [x confirmed]![practice] = [prac name]). With this criteria...

how does one expand individual cells?
I am using Excel for quoting purposes and merging information from the quote to a letter on another tab in the same file. I am curious how to expand individual cells within the letter worksheet. It is easy to expand columns and rows, but I am hoping to customize the size of individual cells. All ideas & opions are welcome and appreciated, Brian As far as I know it is not possible to size individual cells. You might try to use the Cell merge option. This works for adjecent cells in a row (or column). This will give you wider spacing where you need (for long text) it and preserves &q...

automatically creating selected number of records in different table
Hi, I am setting up a database to track insurance policies issued, and I've hit a wall with one item. Each policy can have up to 10 classes, and each class can have an unlimited number of benefits. I have a table for the policies that currently links to a table for the classes, and that links to a table for each benefit applicable to each class. To enhance this, I'd like to add a field to the policy table where the underwriter enters the number of classes (x), and then x number of records is automatically created in the classes table (and each record that was created is numbered co...

List Box Input to query
I orchestrated my first VB yesterday... So I am a novice. I am not sure what you mean... I have used filter and wuto filter before, but how do I incoroprate it into my VB code so it is automated. Currently I manually pull data via ODBC For eg. select x from Y where z > 1000 and dt = 20041231 What I want to be able to do is to input x and z > 1000 and pass it to the query t run. I was able to this with the dt = part yesterday where I pass in the date. And that took a whole day. I am not sure how to do the rest.. like being able to select a item frm a list and have it represent x. ...