Help With Databaseto Produce Catalogue

Aug 27, 2006

Hi, I am looking for help with a database I am trying to set up for a dog
club. I have created databases before although not for more than 6 years
and am now probably at the bottom end of Intermediate in terms of skill and
have no experience of using code builder as yet.
I need to create a database of dogs, their parents, owners and breeders and a few other bits of information such that I can print a catalogue of dogs entered at dog shows (see below) and am finding it very difficult.

Owner(s), address,
name of dog, sex (Dog or Bitch), Date of Birth, breeder, Sire - Dam

I've created various tables: Dogs, Owners, breeders, address, Dogs/Owners
(junction table) and Query to do the pedigree of the Dog (ie the parents)
which is a self join.

An owner can have more than one dog and a dog can have between 1 and 5
owners at the same time. Where there is more than one owner then the
address is that of the first named.

I also need to be able to add and subtract both dogs and owners to/from
their respective tables although subtraction will occur less frequently. I
then need to be able to produce catalogues with the details laid out as
above. Not all of the dogs or owners will be in each catalogue. I
anticipate doing this using a query and then using mailmerge in Word.

I appreciate that this may be too big a task to help me with but do not like
to give up until I have explored all avenues. If you can help then
I can let you know the field and joins that I have and other important
details.

Thanks (hopefully)

Pete

View Replies


ADVERTISEMENT

Produce The Day?

Nov 22, 2006

Hi

I have a table which keeps a record of days off sick.

The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format

and a [Last Day] = last day they were off sick, again in the same date format.

I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday"
and then the last day is 27/10/06, it will produce "Friday"
But, I also need it to produce "Thursday" (which would be the 26/10/06)

Any ideas??

Thanks

Maria

View 14 Replies View Related

How Can This Produce An Error?

Dec 3, 2006

I have 2 comboboxes to select a product no, and a unit of measurement

I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL

However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.

(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)

I have actually got round this by testing the abs difference as being greater than a minuscule value.

ie
If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error

I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?


so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem


function lookupmat as boolean

strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _
" and [pmpunit] = " & cboUnit

Set rs = CurrentDb.OpenRecordset(strsql)

If rs.eof Then
lookupmat = False
rs.Close
GoTo exitproc
End If

'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same!
If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _
"cboProd = " & Format(cboProd, "###.0000000000") & _
" Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _
"cboUnit = " & Format(cboUnit, "###.0000000000") & _
" Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)

'and now both of these tests are showing a difference! of zero
If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno)
If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)

lookupmat = False
rs.Close
GoTo exitproc
End If

View 14 Replies View Related

[Ask] SQL To Produce This Result

Jul 4, 2007

Hi everyone,

I really need help with transforming this table/data:

Project Date(dd/mm/yy) Value
----------------------------------
A...........1/1/2007...............100
B...........2/2/2007...............200
C...........3/2/2007...............300
D...........4/5/2007...............400
E...........5/5/2007...............500
F...........6/5/2007...............600
G...........7/6/2007...............700

into the following data format (13 columns in total, with 7 rows):

Project Jan Feb Mar Apr May Jun Jul ... Dec
-------------------------------------------------------------------
A............100.................................. ..........................................
B.....................200......................... ..........................................
C.....................300......................... ...........................................
D...............................................40 0.........................................
E...............................................50 0.........................................
F...............................................60 0.........................................
G................................................. .........700...............................

Assuming today is January 1st 2007, the 2nd column must starts with current month.

If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.

Thanks in advance..

View 1 Replies View Related

How To Produce & Print In Poerpoint??

Aug 29, 2006

I've got a small employee training tracking database - no problem there. What the users currently do is to run a report from the database that shows who received what training, and when. They then open Powerpoint, pull up a training certificate document and manually type the employee name (by referring to the MS Access report), course taken and date the course was completed.

Here's my question: Can Access automatically pull up or go into Powerpoint and produce these Powerpoint certificates automatically? If so, how would I go about doing this? The users would love me forever (or at least until the next request) if this can be automated.

Yhank you in advance - it's amazing how knowledgeable you folks are!!

View 2 Replies View Related

Best Way To Produce Unique Records

Jul 25, 2005

Hello, hoping for some help on the following scenario:

I need to produce a query which results in one record for each part number.

In a sample table of purchasing history (tblPODetails):
Fields:
PartNumber, Description, Cost, DatePurchased

We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.

I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.

The resulting description field would of course be the one in use for the latest received date.

I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...

View 4 Replies View Related

Produce Expire Date

Dec 11, 2006

I have a "date" field and a "Expire dare" field, what I need is automatically obtain a expire date wich will be the last day on the month of the date field plus 1 year.
example
date = 15/02/2006
expire data 28/02/2007

How can I obtain the result?

View 6 Replies View Related

Produce Report Using Wherecondition

Sep 13, 2005

I'm trying to print a report that displays only records from a particular site.

This is the code I've used:

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stDocName, strSiteName, stLinkCriteria As String

stDocName = "rptExtChkSht"
strSiteName = Me!cmbSiteName
stLinkCriteria = "[SiteName]=" & "'" & strSiteName & "'"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

I'm wondering whether it won't produce only these records because of the design of my report (attached), with the Site Name under the SiteID Header...?? I wouldn't have thought it would matter, but...

Any help greatly appreciated.

View 1 Replies View Related

Need A Query To Produce Current Date

Nov 28, 2006

Guys,

I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.

How do I tell a query that I want to display the current date, and then calculate this?

View 1 Replies View Related

Produce A Report - Using A Drop Down List

Nov 17, 2004

My aim is to produce a report - using a drop down list (of customers from customer table) so that users can look at customer history.



I have created a form with a drop down list of customers - from customers table. I have used this to filter the results for a query on the orders table (gives me a history of that customer). All this works perfectly. I get all the transactions for that customer.



My question - is there a way I can create the same result but in a report format instead of the datasheet format. I.e the user will be given the data in report format when they select the customer from the drop down list.

View 3 Replies View Related

Tables :: Produce A Report Matrix

Mar 25, 2015

I have produced a query, which counts the number of records fulfilling each set of criteria, but I can't now convert that into the matrix presentation.

I have a attached a copy of what is produced currently through Excel.

Basically each count on the query represents one of the boxes within the matrix. For example if the record Impact is 1, and the Likelihood is 1, then it would be counted within the bottom left hand box.

View 6 Replies View Related

How To Produce Single Entry Report

Mar 9, 2013

What I would like to know is this: I have produced a basic members table containing individual's details etc. How do I now produce an individual report for selected members (single page per member), in which I can determine which of the table details are included?

View 2 Replies View Related

Produce Report After Appending Records?

Feb 18, 2013

I have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.

The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.

What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails. It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.

View 14 Replies View Related

Combine Columns.. Forename Surname To Produce..

Nov 27, 2006

Hi

im trying to combine the

Forename Column and Surname column so i can produce another column which is initial surname eg

Matthew | Williamson | M Williams
Kevin | smith | K Smith


how do i do this ive completely gone blank!!!!!! :(((

View 3 Replies View Related

Queries :: Produce HEX And ASCII In Query Results

Sep 17, 2013

We have a need to produce hex and ascii in the same results field.

The field with data will be bound to a 2D barcode (the square ones you see everywhere now). The format of the data is to look like this:

[)><RS>06<GS>xyz<RS><EOT>

The <RS>, <GS>, and <EOT> all need to be hex values as follows:

<RS> = Hex 1E, Decimal 30
<GS> = Hex 1D, Decimal 29
<EOT> = Hex 04, Decimal 04

When I run my Access query as follows:

BarCode: "[)>"&Hex(30)&"06"&Hex(29)&"xyz"&Hex(30)&""&Hex(04)& ""

It produces the following:

[)>1E061Dxyz1E04

So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.

Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.

View 6 Replies View Related

Forms :: Produce Data From A Selected File/ Query

Feb 16, 2015

I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.

I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.

View 14 Replies View Related

Forms :: Produce Datasheet That Derives Its Information From Query

Mar 6, 2013

I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.

In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.

Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".

View 2 Replies View Related

Modules & VBA :: How To Produce ACCDR Files From ACCDB Version

Oct 7, 2013

I have a database (accdb) out of which I want to generate accdr files of which user can only access / view the reports section ONLY. Looking for VB code which will generate employee wise (or any criteria) accdr files?????

View 2 Replies View Related

Query Design To Produce Calculated Value Using Previous Record Value

Jan 28, 2013

I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.

The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.

The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.

Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).

This calculated referencing to the previous record is causing difficulties.

View 2 Replies View Related

Command Button To Produce Form Based On Drop Down Choice?

Mar 8, 2006

Per the instructions detailed here...
http://www.fontstuff.com/access/acctut08.htm

...I created a parameter query in the form of a drop down box that, once an
option is selected, should display a form with a number of fields pre-filled
from a record chosen by the drop-down selection.

The drop down has a command button with the following code in the Onclick
event:

Private Sub cmdCreateReport_Click()
DoCmd.OpenQuery "qryUIRFollowUp", acViewNormal, acEdit
DoCmd.Close acForm, "frmOpenUIRLookUp"

As it now stands when I click the button I get the form but none of the
selected data is filled in. The fields are blank.

What code to I need to add to the above to make the form hold the data
selected from the drop down?

Thanks,
David

View 12 Replies View Related

Reports :: Unable To Produce A Report Which Uses A Static Design And Does Not Change

Jun 13, 2013

I'm trying to produce a report which uses a static deisgn and does not change. For example,I require in the detail of the report a table say 3 * 8 ....and in the report I need the data to fill each one of these cells. However I may not have 8 records , therefore I need the remaing columns/cells empty - the design of 3 * 8 must not change... at the moment I have a dynamic design so if i have 3 records I have 3 *3 table leaving a massive gap on the report.

View 2 Replies View Related

Reports :: Produce A Report By Inputting Parameter Into Text Box On A Form

May 24, 2013

I am very new to access, any way that I can have a popup box appear when I ask to produce a report and I can type in a parameter of my choice and it will produce a report based around that parameter?

I have a table that is directly linked to an excel spread sheet that is updated each week external to the database. I have to produce reports on the data contained in that table.

I have already produced reports that look for specific number and those reports are produced automatically.

I was wondering if there was some way I could open a form and type a number into a text box and it would produce a report around that number. e.g. "list all engines below X margin" and I can type any number in representing X and a report would be produced.

View 9 Replies View Related

Queries :: Find Specific Data In A Table To Produce A Report

May 14, 2013

I have a table that is linked into access 2003. This table is updated by personnel in another location and I have to run a weekly report on engines that are below a certain performance level.

The column heading is MGT Margin and I have to list all of the engines that are below 20 degrees.

Can I run a query that looks at this table and produces a report of all the engines that are below 20 degrees?

I currently have to cut and paste each engine from the updated spread sheet every week onto a separate spread sheet and import that into access. If a query can be used to do what I am after I can use similar principles in other reports I have to run.

View 5 Replies View Related

ERR: The Expression AfterUpdate You Entered As Event Property Setting Produce Error!

Jan 11, 2005

ERR: The expression AfterUpdate you entered as event property setting produce the following error: Return without GoSub....

can someone help me...why..b'coz b4 this i'm using the same coding but it can work for another several form....

View 3 Replies View Related

Queries :: Produce Query Using Combo / Pick List Field Held In Main Table

Jun 17, 2014

My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).

I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.

So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).

Which all works fine

However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.

But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.

I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.

View 1 Replies View Related

Queries :: Call Two Different Queries From Report Wizard To Produce Report?

Jul 31, 2013

I am looking to call two different queries from report wizard to produce report. Getting error message what to do in this situation as both queries are important as i have to pick all records from query A and just one record from query B any other option to get this in report.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved