Issues with Queries & Reports

  • Thread starter Thread starter StevieD via AccessMonster.com
  • Start date Start date
S

StevieD via AccessMonster.com

Hi there. I had this problem a number of weeks ago, it was left but now I
need to get it sorted. Im hoping someone can assist. I shall try to explain...

_______________________________________________________________________________

I have a Table called 'Despatch Status' which contains 3 fields.
- 'MachineCodeA'
- 'MachineCodeB'
- 'MachineCodeC'

Each of these fields is a dropdown list which is populated from another Table
called 'MachineCode'.
In this table theres are two fields entitled ID (primary key) and MachineCode
(the actual value(s) I require).

Basically, I have a Report (used to select and print a date range) called
'Live Orders'.
On this report I need to show the values of MachineCodeA etc.. However, its
simply pulling in the ID number instead of the Machine Code value that I
require?

Im no particularly great at using Access so any 'hand-holding' would be
greatly appreciated.
Many thanks.
 
StevieD said:
Hi there. I had this problem a number of weeks ago, it was left but now I
need to get it sorted. Im hoping someone can assist. I shall try to explain...

_______________________________________________________________________________

I have a Table called 'Despatch Status' which contains 3 fields.
- 'MachineCodeA'
- 'MachineCodeB'
- 'MachineCodeC'

Each of these fields is a dropdown list which is populated from another Table
called 'MachineCode'.
In this table theres are two fields entitled ID (primary key) and MachineCode
(the actual value(s) I require).

Basically, I have a Report (used to select and print a date range) called
'Live Orders'.
On this report I need to show the values of MachineCodeA etc.. However, its
simply pulling in the ID number instead of the Machine Code value that I
require?
Click to expand...


Create a query that includes the Despatch Status table and
three copies of the MachineCode table. Drag a connecting
line from the MachineCodeA field to the ID field in one copy
of the MachineCode table. Then drag a connecting line from
the MachineCodeB field to the ID field in the second copy of
the MachineCode table. Similarly for MachineCodeC.

Now, you can drag the MachineCode field from each table down
to the query's Field list. To avoid the issue of having
three fields named MachineCode add an alias name to these
three fields so the entry looks like MCA: MachineCode,
MCB: MachineCode, etc.

Next open the report in design view and change the report's
RecordSource property from the Despatch Status table to the
name of the new query. And, finally, you can use text boxes
in the report bound to the MCA, MCB, etc. fields to display
the values you require.
 
Hi. Thanks for that, thats great.

The only issue im having is that there are already a number of fields on this
report already coming from another RecordSource called 'ShipList'.

Is there no way to have 2 RecordSources on a single report?


Marshall said:
Hi there. I had this problem a number of weeks ago, it was left but now I
need to get it sorted. Im hoping someone can assist. I shall try to explain...
Click to expand...
[quoted text clipped - 16 lines]
simply pulling in the ID number instead of the Machine Code value that I
require?
Click to expand...

Create a query that includes the Despatch Status table and
three copies of the MachineCode table. Drag a connecting
line from the MachineCodeA field to the ID field in one copy
of the MachineCode table. Then drag a connecting line from
the MachineCodeB field to the ID field in the second copy of
the MachineCode table. Similarly for MachineCodeC.

Now, you can drag the MachineCode field from each table down
to the query's Field list. To avoid the issue of having
three fields named MachineCode add an alias name to these
three fields so the entry looks like MCA: MachineCode,
MCB: MachineCode, etc.

Next open the report in design view and change the report's
RecordSource property from the Despatch Status table to the
name of the new query. And, finally, you can use text boxes
in the report bound to the MCA, MCB, etc. fields to display
the values you require.
Click to expand...
 
No, a report (or form) can only have a single souce of data.
If you were able to get the desired report before (aside
from the machine code issue), then this solution will not
interfere with that.

If this is a separate question, then I will need more
information before I could offer a useful suggestion.
--
Marsh
MVP [MS Access]

Hi. Thanks for that, thats great.

The only issue im having is that there are already a number of fields on this
report already coming from another RecordSource called 'ShipList'.

Is there no way to have 2 RecordSources on a single report?


Marshall said:
Hi there. I had this problem a number of weeks ago, it was left but now I
need to get it sorted. Im hoping someone can assist. I shall try to explain...
Click to expand...
[quoted text clipped - 16 lines]
simply pulling in the ID number instead of the Machine Code value that I
require?
Click to expand...

Create a query that includes the Despatch Status table and
three copies of the MachineCode table. Drag a connecting
line from the MachineCodeA field to the ID field in one copy
of the MachineCode table. Then drag a connecting line from
the MachineCodeB field to the ID field in the second copy of
the MachineCode table. Similarly for MachineCodeC.

Now, you can drag the MachineCode field from each table down
to the query's Field list. To avoid the issue of having
three fields named MachineCode add an alias name to these
three fields so the entry looks like MCA: MachineCode,
MCB: MachineCode, etc.

Next open the report in design view and change the report's
RecordSource property from the Despatch Status table to the
name of the new query. And, finally, you can use text boxes
in the report bound to the MCA, MCB, etc. fields to display
the values you require.
Click to expand...
Click to expand...
 
Hi.
I actually just tried to open the new machinecode query and it pops up 'Type
mismatch in expression'?
Why might this be?

With regards to your last post:
The report as it stood had info from another Query called 'ShipList', this
being the RecordSource.
I basically need to add these 3 new machinecode values (from the new query)
to this same report.



Marshall said:
No, a report (or form) can only have a single souce of data.
If you were able to get the desired report before (aside
from the machine code issue), then this solution will not
interfere with that.

If this is a separate question, then I will need more
information before I could offer a useful suggestion.
Hi. Thanks for that, thats great.
Click to expand...
[quoted text clipped - 27 lines]
Click to expand...
 
So your these last two posts boil down to the question about
why you are getting a type mismatch error, right? Generally
that message means you omitted some quotes around a text
field value or you are using a string with a numeric field.

How about posting a Copy/Paste of your query's SQL view
along with an explation of the field types.
--
Marsh
MVP [MS Access]

I actually just tried to open the new machinecode query and it pops up 'Type
mismatch in expression'?
Why might this be?

With regards to your last post:
The report as it stood had info from another Query called 'ShipList', this
being the RecordSource.
I basically need to add these 3 new machinecode values (from the new query)
to this same report.


Marshall said:
No, a report (or form) can only have a single souce of data.
If you were able to get the desired report before (aside
from the machine code issue), then this solution will not
interfere with that.

If this is a separate question, then I will need more
information before I could offer a useful suggestion.
Hi. Thanks for that, thats great.
Click to expand...
[quoted text clipped - 27 lines]
in the report bound to the MCA, MCB, etc. fields to display
the values you require.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top